Excel like a finance pro.
RANK.EQ & RANK.AVG
AggregationCustomer concentration, top-N lists and ABC analysis — and ties are where it trips.
Difficulty
1What is it?
RANK.EQ gives each value its position in a list, 1 for the largest by default. It's the backbone of customer concentration and ABC analysis: rank the customers, then see what the top five are worth. The part that catches people is ties. Two customers on the same revenue both rank 2 under RANK.EQ and the next one ranks 4 — there is no 3. RANK.AVG gives both 2.5 instead, which keeps the average rank correct and is what a statistical reading wants.
2What it looks like
RANK.EQ(number, ref, [order]) · RANK.AVG(number, ref, [order])- number
- The value to rank.
- ref
- The list it's ranked within. Lock it with $ before copying down, or the list slides.
- [order]
- 0 or omitted = largest is 1 (revenue). 1 = smallest is 1 (days to pay, cost).
3When you use it
- Rank customers by revenue for a concentration analysis.
- Flag the top 10 accounts by spend for a review.
- Order suppliers by days to pay, smallest first.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Make two customers equal and watch RANK.EQ jump a number.
=RANK.EQ(B2, $B$2:$B$5)Globex EQ 1 AVG 1| A | B | C | |
|---|---|---|---|
| 1 | Customer | Revenue | Result |
| 2 | Globex | 98,000 | Globex EQ 1 AVG 1 |
| 3 | Acme | Acme EQ 2 AVG 2.5 | |
| 4 | Umbrella | 63,000 | Umbrella EQ 2 AVG 2.5 |
| 5 | Initech | 27,500 | Initech EQ 4 AVG 4 |
| 6 | Acme and Umbrella both rank 2 under EQ, and nobody is 3. AVG gives both 2.5. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Ranks skip a numberA tie — two values share a rank and the next one jumps.Fix: Correct for RANK.EQ. Use RANK.AVG if the ranks feed an average, or add a tiny tiebreaker like +ROW()/1E9.
Every rank is 1, or ranks drift down the columnref wasn't locked, so it moved as the formula was copied.Fix: $B$2:$B$40 — absolute. This is most rank bugs.
#N/AThe number isn't in the ref list at all.Fix: Rank a value from the same list you're ranking within.
Biggest ranks lastorder was 1 when you meant largest-first.Fix: Omit it, or pass 0, for largest = 1.
6Better functions & alternatives
- SORT + TAKE — When you want the ordered LIST rather than a rank number beside each row.
- LARGE / SMALL — Answers the reverse question: what is the 3rd largest value, rather than what rank is this one.
Want RANK.EQ & RANK.AVG already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.