Microsoft Office Tutorials and References

In Depth Information

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

•
average_range

average_range
—
One or more cells to average, including numbers or

names, arrays, or references that contain numbers.

•
criteria_range1, criteria_range2, ...

criteria_range1, criteria_range2, ...
—
One to 127 ranges in which to

evaluate the associated criteria.

•
criteria1, criteria2, ...

criteria1, criteria2, ...
—
One to 127 criteria in the form of a number,

expression, cell reference, or text that defines which cells will be

averaged. For example, criteria can be expressed as 32, "32", ">32",

"apples", or B4.

Using

Using
RANK

to Calculate the Position Within a List

At times you need to determine the order of values, but you are not allowed to

sort the data. The RANK function helps with this task.

RANK
to Calculate the Position Within a List

Note

Excel gurus have been complaining about an anomaly with the RANK

function. Apparently, a bunch of scientists have also been complain-

ing about RANK. Microsoft fixed the RANK function in Excel 2010. But

they listened to the complaints of the scientists instead of the gurus.

So, now there are two rank functions and neither one is going to make

the Excel pros happy.

Suppose five bowlers scored 187, 185, 185, 170, and 160. The traditional way

to rank the players is that two players would have a rank of 2, and the next

player would have a rank of 4. No one would be ranked number 3. Although

this is technically correct, it can cause problems if you have lookup val-

ues expecting to find a person ranked number 3. The example at the end of this

section explains how to overcome such a situation. The clever Excellers who

hoped to use RANK to sort a list using formulas really want RANK to return

one of every rank.

As of Excel 2010, Microsoft renamed the old RANK as RANK.EQ. It added a new

rank called RANK.AVG. In the same situation with the five bowlers, both of

the scores of 185 would get a rank of 2.5. A rank of 2.5 is the average of the

ranks of 2 and 3.

Syntax

=RANK.EQ(number,ref,order)