Rank Function

[azlan]

Hi,

RANK fuction gives duplicate numbers the same rank. However, the presence of
duplicate numbers affects the ranks of subsequent numbers. For example, in a
list of integers sorted in ascending order, if the number 10 appears twice
and has a rank of 5, then 11 would have a rank of 7 (no number would have a
rank of 6). How can i adjust the formula so that number 11 would be ranked 6
instead of 7?

Thank you

 

[Bob Phillips]

=SUM(IF(A1>$A$1:$A$10,1/COUNTIF($A$1:$A$10,$A$1:$A$10)))+1

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)