RANK function
RANK returns the position of a number within a list, such as a salesperson against the team. It is a compatibility function; RANK.EQ and RANK.AVG replace it in new workbooks.
Example
Read it as a sentence: find the rank of 7 in AJ1:AJ5, counting the largest value as 1, which gives 2.
Excel shows 2
AJ
1
7
2
3
3
3
4
9
5
1
Data
Syntax
RANK(number, ref, [order])
Arguments in [square brackets] are optional.
-
number(required) - Value to rank.
-
ref(required) - Range of numbers to rank within.
-
order(optional) - 0 or omitted ranks largest as 1; any other number ranks smallest as 1.
Returns: The position of number in the list.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=RANK(7,Data!AJ1:AJ5) | 2 | Descending (default) |
=RANK(3,Data!AJ1:AJ5) | 3 | Tie descending |
=RANK(3,Data!AJ1:AJ5,1) | 2 | Ascending order 1 |
=RANK(3,Data!AJ1:AJ5,5) | 2 | Order nonzero = ascending |
=RANK({7;3},Data!AJ1:AJ5) | 2 | Array number (spills) |
=RANK(Data!AJ1:AJ5,Data!AJ1:AJ5) | 2 | Whole column of the ref (spills) |
=RANK(4,Data!AJ1:AJ5) | #N/A | Number not in ref |
=RANK("abc",Data!AJ1:AJ5) | #VALUE! | Text number |
The Data sheet these formulas read
A cell that starts with = holds that formula.
AJ
1
7
2
3
3
3
4
9
5
1
Data
Watch out for
- Superseded by RANK.EQ / RANK.AVG; use that in new workbooks.
- Ties share a rank and skip the next one.
- Lock the ref with $ when copying down.
- ref is meant to be a range; an array constant may not be accepted.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- RANK is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .