- Excel Book Excerpt
Excel Rank a List Without Ties
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: How are ties handled when ranking?
Strategy: Excel 2010 offers new ways to handle ties when ranking. In this figure, products B & D are tied with sales of 87. The old RANK and RANK.EQ functions assign both of those products a rank of 2 and no product is ranked as 3.
Figure 348 Various ways to rank values.
Statisticians argue that products B & D should each receive a rank of 2.5, since the average of ranks 2 & 3 is 2.5. The new Excel 2010 function RANK.AVG will handle ties in this fashion.
Excel tricksters who use RANK to sort with a formula as described in the next topic want to make sure that every rank is used exactly once. They will use the formula shown in column G. This formula uses the original RANK function and then adds 1 if the ranked value is appearing a second time in the list.
For more resources for Microsoft Excel: