Microsoft Office Tutorials and References
In Depth Information
Using RANK to Calculate the Position Within a List
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.
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
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.