Microsoft Office Tutorials and References
In Depth Information
Using RANK to Calculate the Position Within a List
Figure 14.6. In this case,
In this case, RANK.EQ
RANK.AVG return different values
return different values
when a tie occurs.
Column D uses the new RANK.AVG to rank the scores. Note the differences in
rows 8 and 9. The tied values both receive a lower rank of 10 with RANK.EQ
and a value of 10.5 with RANK.AVG. None of the records are ranked 11.
If you need the lowest value to be ranked as number 1, add a third argument
of 1 to indicate that the lowest number is the best — for example,
A common Excel trick is to use the ranking function combined with VLOOKUP
or MATCH to sort a range with a formula. You might assign ranks and then
use VLOOKUP to find the people who are ranked first, second, and third.
The VLOOKUP function certainly is not expecting two people to be ranked at
number 2 as RANK.EQ would do. It definitely would never expect the duplic-
ate 2.5 values that RANK.AVG would return. The generally accepted solution
is to use RANK.EQ and then add a COUNTIF function that checks to see how
many rows above this row have the identical value.
In Figure 14.7 , examine the formula in cell C7. COUNTIF asks how many times
the value in cell B7 was found in B$2:B6. This final reference is an inter-
esting reference. It tells Excel to count always from row 2 down to the row