RANK.AVG Function
Returns a number's rank in a list; ties get the average rank.
Category
Statistical
Available in
Excel 2010+, Microsoft 365
Returns
A number
Want to practice RANK.AVG in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox
Overview
RANK.AVG works like RANK.EQ, except tied values get the average of the ranks they cover. Two values tied for 5th and 6th both get 5.5.
That's the convention statistical tests (such as rank correlation) expect.
Syntax
=RANK.AVG(number, ref, [order])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| number | Yes | The value to rank. |
| ref | Yes | The list to rank it in. Lock it with $ when filling down. |
| order | No | 0 or left out = largest is 1; 1 = smallest is 1. |
Examples
Example 1 — Sales ranking with averaged ties
Compare with RANK.EQ, which gives Ben and Gus 5.
D2fx=RANK.AVG(C2,$C$2:$C$8)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Region | Sales | Rank |
| 2 | Ana | North | 62000 | 2 |
| 3 | Ben | South | 48500 | 5.5 |
| 4 | Cy | North | 50000 | 4 |
| 5 | Di | East | 71250 | 1 |
| 6 | Ed | South | 39800 | 7 |
| 7 | Fay | North | 55400 | 3 |
| 8 | Gus | East | 48500 | 5.5 |
=RANK.AVG(C2,$C$2:$C$8) Explain
Common mistakes
- #N/A The number isn't in ref.
Notes
- The ranks from RANK.AVG always add up to the same total as 1 + 2 + … + n.
- Related functions: RANK.EQ, PERCENTRANK.INC, MEDIAN.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.