Excel Ranking Functions (RANK, RANK.EQ, and RANK.AVG): Handling Ties and Ranking by Group
Use Excel ranking functions to instantly rank scores or sales figures. This guide covers the differences between RANK, RANK.EQ, and RANK.AVG, along with ranking by group, top N rankings, and tiebreakers using practical examples.
Quick Fix (3 Minutes)
// Basic descending order
=RANK.EQ(E2, $E$2:$E$21, 0)
// Ascending order
=RANK.EQ(E2, $E$2:$E$21, 1)
// Average rank for ties
=RANK.AVG(E2, $E$2:$E$21, 0)
// Rank by group (dynamic arrays)
=RANK.EQ(E2, FILTER($E$2:$E$21, $B$2:$B$21=B2), 0)
// Top N label
=IF(RANK.EQ(E2,$E$2:$E$21,0)<=3,"TOP3","")
Function Overview
- RANK.EQ: Gives tied values the same rank (1, 1, 3). order: 0 = descending, 1 = ascending.
- RANK.AVG: Assigns the average rank to tied values (such as 1.5).
Rank by Group
Dynamic Arrays
=RANK.EQ(E2, FILTER($E$2:$E$100, $B$2:$B$100=B2), 0)Legacy Excel
=1 + COUNTIFS($B$2:$B$100, B2, $E$2:$E$100, ">"&E2)Top/Bottom N
=IF(RANK.EQ(E2,$E$2:$E$21,0)<=3,"🥇TOP 3","")
=IF(RANK.EQ(E2,$E$2:$E$21,1)<=5,"⚠LOW 5","")Tiebreaker
// Assign consecutive numbers in order of appearance
=RANK.EQ(E2,$E$2:$E$21,0)+COUNTIFS($E$2:$E$21,E2,$E$2:$E2,E2)-1Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Every rank is 1 | The reference range shifts | Lock the ref range with $ absolute references |
| Ranking by group does not work | Missing filter or criteria | Add the group criterion to FILTER or COUNTIFS |
| Ascending/descending order is reversed | Confusing the order argument | 0 = descending, 1 = ascending |
| Tie handling differs from expectations | Difference between EQ and AVG | Use the function that fits your intended result |
Related Posts
- SUMIFS and AVERAGEIFS Practical Patterns
- MAXIFS and MINIFS Conditional Maximums and Minimums
- Data Preparation with TEXTSPLIT
- Get Started with Excel PivotTables in 10 Minutes
- VLOOKUP with Multiple Criteria