RANK.EQ function
RANK.EQ gives the standard leaderboard rank of a number within a list. Tied values share the same rank and the next rank is skipped, so results can run 1, 2, 2, 4.
Example
Read it as a sentence: find the rank of 7 in AJ1:AJ5, counting the largest value as 1 and giving tied values the same rank, which gives 2.
Excel shows 2
AJ
1
7
2
3
3
3
4
9
5
1
Data
Syntax
RANK.EQ(number, ref, [order])
Arguments in [square brackets] are optional.
-
number(required) - The value to rank.
-
ref(required) - The list of numbers to rank against.
-
order(optional) - 0 or omitted ranks largest first; any other number ranks smallest first.
Returns: The rank of the number; ties share the top rank.
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.EQ(7,Data!AJ1:AJ5) | 2 | Descending (default) |
=RANK.EQ(3,Data!AJ1:AJ5) | 3 | Tie descending |
=RANK.EQ(3,Data!AJ1:AJ5,1) | 2 | Ascending order 1 |
=RANK.EQ(3,Data!AJ1:AJ5,5) | 2 | Order nonzero = ascending |
=RANK.EQ({7;3},Data!AJ1:AJ5) | 2 | Array number (spills) |
=RANK.EQ(Data!AJ1:AJ5,Data!AJ1:AJ5) | 2 | Whole column of the ref (spills) |
=RANK.EQ(4,Data!AJ1:AJ5) | #N/A | Number not in ref |
=RANK.EQ("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
- Ties leave gaps (1, 2, 2, 4).
- Lock the ref range with $ when filling down.
- 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.EQ arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, but behaves differently. Sheets has RANK and RANK.EQ.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .