Rank a List Without Ties

February 17, 2022 - by Bill Jelen

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.

The various rank functions behave differently when there is a tie. There are 7 numbers being ranked, with a two-way tie for 2nd. RANK and RANK.EQ provide two numbers ranked as 2 and no one ranked as 3. RANK.AVG provides two items ranked as 2.5 and nothing ranked as 2 or 3. The custom formula of RANK + COUNTIF makes sure that all 7 ranks appear exactly once. — Figure 340. 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

This article is an excerpt from Power Excel With MrExcel

Title photo by Emmanuel Boldo on Unsplash