Problem: How are ties handled when ranking?
Strategy: Excel 2010 introduced 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.
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.
- Various ways to rank values.
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.
=RANK($B2,$B$2:$B$8)+COUNTIF(B$2:B2,B2)-1