The Excel RANK.AVG function returns the rank of a number within a list. When several values tie, RANK.AVG gives each of them the average of the ranks they would otherwise occupy.
Syntax
| Argument | Description | |
|---|---|---|
number | Required | The value whose rank you want to find. |
ref | Required | The array or range of numbers to rank against. Non-numeric values are ignored. |
order | Optional | 0 or omitted = descending (largest is rank 1); any non-zero value = ascending (smallest is rank 1). |
How to use it
RANK.AVG works exactly like the older RANK function, except in how it treats ties. By default it ranks in descending order — the largest number is rank 1.
The two 5s would occupy ranks 2 and 3. RANK.AVG averages those positions, so both report 2.5. Pass a non-zero order to rank ascending instead:
RANK.AVG vs RANK.EQ: use RANK.AVG when ties should share an averaged position (fairer for percentiles and scoring); use RANK.EQ when ties should all get the same top rank.
Try it: interactive demo
Pick a RANK.AVG example to see the formula and its result.
Practice workbook
Frequently asked questions
How does RANK.AVG handle ties?
2.5.What is the difference between RANK.AVG and RANK.EQ?
How do I rank smallest-to-largest?
=RANK.AVG(number, ref, 1) ranks in ascending order so the smallest value is rank 1.Does the old RANK function still work?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class