The RANK.EQ function in Excel
RANK.EQ tells you where a number stands in a list: 1 is the largest (or the smallest, if you ask for ascending order).
Equal numbers get the same rank and the next rank is skipped, like in a sports table. The older name RANK still works and gives the same result.
Syntax
=RANK.EQ(number, ref, [order])
| Argument | What it means |
|---|---|
| number | The value to rank. |
| ref | The list of numbers to rank it in. Use absolute references (like $B$2:$B$6) when you copy the formula down. |
| orderoptional | 0 or left out: highest is 1. Any other number, such as 1: lowest is 1. |
Examples
1. Rank from the highest
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Rank | |
| 2 | Ana | 70 | ||
| 3 | Ben | 85 | ||
| 4 | Cy | 85 | ||
| 5 | Dee | 90 | 2 | |
| 6 | Eli | 100 |
Only Eli's 100 is higher than Dee's 90, so Dee is second.
2. Rank from the lowest
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Lowest first | |
| 2 | Ana | 70 | 1 | |
| 3 | Ben | 85 | ||
| 4 | Cy | 85 | ||
| 5 | Dee | 90 | ||
| 6 | Eli | 100 |
With order 1, the smallest value is rank 1. Ana's 70 is the lowest score.
3. Ties share a rank
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Rank | |
| 2 | Ana | 70 | ||
| 3 | Ben | 85 | 3 | |
| 4 | Cy | 85 | ||
| 5 | Dee | 90 | ||
| 6 | Eli | 100 |
Ben and Cy both have 85, so both are third. Nobody is fourth, and Ana's 70 is fifth.
Common mistakes
Forgetting to lock the list. After copying the formula down,
=RANK.EQ(B3,B3:B7)ranks inside a shrinking range.Fix: Use $B$2:$B$6 for the list so it stays the same in every row.
Expecting ties to get different ranks. Two equal values always get the same rank.
Fix: To break ties, add a tiny tie-breaker with COUNTIF or use SORT on the list.
Frequently asked questions
What is the difference between RANK, RANK.EQ and RANK.AVG?
RANK and RANK.EQ give tied values the same top rank. RANK.AVG gives them the average of their ranks (3.5 instead of 3 for two values sharing places 3 and 4).