Yes, RANK returns a position number based on value order. But if you think it always breaks ties the same way across versions—or that RANK.EQ is just RANK with a new name—you’ll misread your sales leaderboard.
Quick Answer
RANK (and its successors RANK.EQ and RANK.AVG) assigns ordinal positions to numbers in a list—but RANK and RANK.EQ assign identical ranks to tied values and skip subsequent ranks, while RANK.AVG returns the average rank for ties. All three ignore text, logical values, and blanks; only numeric cells in the reference range count.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| RANK (legacy) | =RANK(A2,$A$2:$A$11,0) — third argument 0=descending, 1=ascending | Backward compatibility with Excel 2003–2010 files | Deprecated; no tie-handling option; inconsistent behavior with arrays |
| RANK.EQ | =RANK.EQ(B5,$B$2:$B$11,0) — identical logic to RANK, but stable and supported | Most ranking tasks where ties should share rank and next rank is skipped | Still skips ranks after ties (e.g., two #1s → next is #3) |
| RANK.AVG | =RANK.AVG(C7,$C$2:$C$11,1) — ascending order, averages tied positions | Academic grading, performance reviews, or reports requiring statistical fairness | Returns decimals (e.g., 2.5), which break integer-only workflows like INDEX/MATCH lookups |
| COUNTIFS + array logic | =COUNTIFS($D$2:$D$11,">"&D2)+1 — manual rank without skipping ties | Custom ranking where ties get sequential numbers (e.g., 1, 2, 2, 4) | Slower on >10k rows; requires Ctrl+Shift+Enter in pre-365 versions |
Method 1 Deep Dive
Open a blank sheet. Paste this into A1:C11:
| Salesperson | Q1 Revenue ($) | RANK.EQ Result |
|---|---|---|
| Sarah Chen | 48200 | =RANK.EQ(B2,$B$2:$B$11,0) |
| Diego Mora | 48200 | =RANK.EQ(B3,$B$2:$B$11,0) |
| Amina Patel | 45200 | =RANK.EQ(B4,$B$2:$B$11,0) |
| James Wu | 42100 | =RANK.EQ(B5,$B$2:$B$11,0) |
| Lena Torres | 39800 | =RANK.EQ(B6,$B$2:$B$11,0) |
| Kenji Tanaka | 39800 | =RANK.EQ(B7,$B$2:$B$11,0) |
| Maya Dubois | 37500 | =RANK.EQ(B8,$B$2:$B$11,0) |
| Omar Hassan | 35200 | =RANK.EQ(B9,$B$2:$B$11,0) |
| Tara Kim | 32100 | =RANK.EQ(B10,$B$2:$B$11,0) |
| Eli Reed | 29800 | =RANK.EQ(B11,$B$2:$B$11,0) |
You’ll get: Sarah and Diego both rank 1. Amina is 3. James is 4. Lena and Kenji are both 5. Maya is 7. Notice the gaps: no rank 2, no rank 6. That’s RANK.EQ’s design—not a bug. It answers “what position would this value hold *if* all higher values were unique?”
Counterintuitive tip: If you sort the list first (Data → Sort, Alt+A+S+S), RANK.EQ still uses original positions—not sorted row order. Always anchor the range with $ signs. And never use =RANK.EQ(B2,B:B,0)—it scans 1M+ cells and crashes responsiveness.
Method 2 Deep Dive
Now replace column C formulas with RANK.AVG. In C2, enter:
=RANK.AVG(B2,$B$2:$B$11,0)
Copy down. You’ll see Sarah and Diego now both show 1.5. Lena and Kenji show 5.5. Why? Because RANK.AVG calculates the average of the positions those tied values *would occupy*. Two items tied for 1st and 2nd → (1+2)/2 = 1.5. Two items tied for 5th and 6th → (5+6)/2 = 5.5.
This matters when feeding results into charts or dashboards. A bar chart plotting RANK.AVG will misalign if axis labels expect integers. Also: RANK.AVG ignores the third argument’s meaning for ties—it only changes sort direction, not tie resolution.
Try this test: In D2, type =RANK.AVG(48200,$B$2:$B$11,0). It returns 1.5—even though 48200 appears twice. That’s correct. But if you paste that same formula into D3, it also returns 1.5. So RANK.AVG is stable, but not position-aware per cell—it’s value-aware.
Pro move: Press Alt + M + V to open Evaluate Formula (Formulas tab → Evaluate Formula). Step through RANK.AVG on B2. Watch how Excel resolves the tie before returning the average. Do this once. You’ll never second-guess it again.
Cheat Sheet
| Task | Formula | Shortcut / Tip | Cell Reference Note |
|---|---|---|---|
| Rank highest to lowest | =RANK.EQ(A2,$A$2:$A$25,0) |
Alt+M+V to debug ties | Lock range with $A$2:$A$25 |
| Rank lowest to highest | =RANK.EQ(A2,$A$2:$A$25,1) |
Use 1 — not TRUE — for ascending | Never use A:A — too slow |
| Average rank for ties | =RANK.AVG(B5,$B$2:$B$11,0) |
Returns decimals — round only if needed | Works identically in Excel 2010+ |
| No-skip tie handling (1,2,2,3) | =COUNTIFS($C$2:$C$11,">"&C2)+1 |
Ctrl+Shift+Enter required in Excel 2019– | Faster than array formulas in 365 |
