Microsoft Office Tutorials and References

In Depth Information

**Using RANK to Calculate the Position Within a List**

Figure 14.6.

Figure 14.6.
In this case,

In this case, RANK.EQ

RANK.EQ and

and RANK.AVG

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,

=RANK(B2,$B$2:$B$13,1).

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