Rank a rep when two reps are tied (RANK.EQ)
The problem
Problem statement
Revenue per rep is in B2:B5. Put the rank of the rep on row 3 in the Rank of row 3 cell D2. Two reps are level, so read carefully what a tie should produce before you write the formula.
Level reps share a rank, and the next rank is skipped — do not invent a tie-breaker.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Rep | Revenue | Rank of row 3 |
| 2 | Ada | 500 | |
| 3 | Ben | 300 | |
| 4 | Cleo | 300 | |
| 5 | Dan | 100 |
Notes
RANK.EQ(value, range) gives the descending rank by default and assigns the same rank to equal values. That is why a four-person list with a tie reads 1, 2, 2, 4 rather than 1, 2, 2, 3. Counting how many values are greater and adding one is the same rule without the function.
How to approach
Your answer goes in D2.
- Target cell is D2
- The value to rank is B3
- Count the values greater than B3 and add one
- Shape: =RANK.EQ(?, ?)
FAQ
How do ties work in RANK.EQ?
Equal values get the same rank and the following rank is skipped: 1, 2, 2, 4. RANK.AVG would give both tied rows 2.5 instead.
How do I rank ascending instead of descending?
Pass 1 as the third argument: =RANK.EQ(value, range, 1). A 0 or omitted third argument ranks highest-first.