Microsoft Office Tutorials and References

In Depth Information

**Ranking Values**

Ranking Values

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

place.

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.