Excel like a finance pro.
← The libraryPractice · 3 questions →
Statistical function
RANK.AVG
Returns the rank of a number; ties get the average rank.
OccasionalDifficulty 1150 · ProficientUsage rank #186 of 520
When to use it
Like RANK.EQ, but tied values receive the average of the ranks they would occupy. Standard in statistics for rank-based tests.
The shape of it
- Syntax
=RANK.AVG(number, ref, [order])
Worked examples
Tied values
=RANK.AVG(85,{70,85,90,85}) → 2.5
Two 85s occupy ranks 2 and 3, so each gets 2.5.
Untied value
=RANK.AVG(90,{70,85,90,85}) → 1
No tie, so a whole number.
Ascending
=RANK.AVG(70,{70,85,90,85},1) → 1
Ascending order.
Worth knowing
- Use it for Spearman correlation: CORREL of the two RANK.AVG columns.
- For leaderboards RANK.EQ reads better.
- Ties produce .5 values, so format with one decimal.
Where it goes wrong
- #N/A when the number is not in the list.
- Non-integer ranks confuse people who expect a position.
Related
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.