Microsoft Office Tutorials and References
In Depth Information
Often, computing the rank order for the values in a range of data is helpful. For example, if you
have a worksheet containing the annual sales figures for your sales staff, you might want to know
how each person ranks, from highest to lowest.
If you’ve used the RANK function, you might have noticed that the ranks produced by this
function don’t handle ties the way that you might like. For example, if two values are tied for third
place, the RANK function gives both of them a rank of 3. You might prefer to assign each an
average (or midpoint) of the ranks — in other words, a rank of 3.5 for both values tied for third
Excel 2010 includes a new function, RANK.AVG, that corrects this problem.
Figure 98-1 shows a worksheet that uses two methods to rank a column of values. The first
method (column C) uses the RANK function, and column D uses the new RANK.AVG function.
The range B2:B16 is named Sales.
Figure 98-1: Ranking data with the Excel RANK function and with array formulas.
Notice that two people are tied for third place. The RANK function assigns each person a rank of
3, but the RANK.AVG function gives each one a rank of 3.5. Also, three people are tied for tenth
place. The RANK function gives each one a rank of 10. The RANK.AVG function, on the other
hand, gives these three people a rank of 11, which is the average of 10, 11, and 12.
Formulas that use RANK.AVG function will return an error in previous versions of Excel. If you
plan to share your workbook with someone who uses a version prior to Excel 2010, you can use
an array formula instead of the RANK.AVG function.