Microsoft Office Tutorials and References
In Depth Information
Using RANK to Calculate the Position Within a List
Neither RANK.EQ nor RANK.AVG calculates in Excel 2007 or earlier. In leg-
acy versions of Excel, use the RANK function to calculate RANK.EQ. There
was no equivalent of RANK.AVG in versions of Excel before 2010.
The RANK functions return the rank of a number in a list of numbers. The
rank of a number is its size relative to other values in a list. If you were
to sort the list, the rank of the number would be its position. This function
takes the following arguments:
number — This is the number whose rank you want to find.
ref — This is an array of, or a reference to, a list of numbers. Non-
numeric values in refare ignored.
order — This is a number that specifies how to rank number. For a
value of 0 or if this argument is omitted, Excel ranks numberas if ref
were a list sorted in descending order. If orderis any nonzero value,
Excel ranks numberas if refwere a list sorted in ascending order.
RANK.EQ gives duplicate numbers the same lower rank. However, the presence
of duplicate numbers affects the ranks of subsequent numbers. For example,
in a list of integers, 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.)
RANK.AVG gives duplicate numbers the same rank by averaging the ranks of
the next two positions. In the same example, if the number 10 appears twice and
would hold the number 5 and 6 positions, both of the 10s would receive an av-
erage rank of 5.5.
is =RANK.EQ(B2,$B$2:$B$13). Notice that the third argument is omitted, so the
highest score is ranked as number 1. Also notice that the second argument is
marked as absolute so that the formula can be copied, and it always points
to the same ref range.