Rank formula
Maybe this...
Assuming your lookup array goes from 100 to 0 and the number to rank will
not be greater than 100.
=MATCH(D1,A1:A11,-1)+(COUNTIF(A1:A11,D1)=0)
--
Biff
Microsoft Excel MVP
"Daniel Bonallack" wrote in
message ...
I need a formula that will rank a given value against a set of other
values.
For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e.
sorted in descending order.
I enter the number 63 in cell D1. In cell E1 I want a ranking formulat
that
returns the number 5, as this would be the rank of this number if it were
in
the array in column B.
Can you help? I do know that if I sort column B ascending, then I could
just use a vlookup formula, but this is not possible for a number of
reasons.
Thanks!
Daniel
|