Microsoft Office Tutorials and References

In Depth Information

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

=RANK.AVG(number,ref,order)

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

number
—
This is the number whose rank you want to find.

•
ref

ref
—
This is an array of, or a reference to, a list of numbers. Non-

numeric values in refare ignored.

•
order

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.

In
Figure 14.6
,
column B contains a list of scores. The formula for cell C2

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.