RANK gives the position of a value within a list, counting from the largest by default or from the smallest if you ask.
Syntax
RANK(number, ref, [order])
When to use it
League tables, top performers, placing a score against everyone else in the column.
What trips people up
Equal values share a position and the next one is skipped, so two firsts are followed by a third rather than a second. Pin the range with dollar signs before filling down, or each row ranks against a different set of rows.
Add 1 as the third argument: =RANK(B2,B$2:B$20,1). Leave it out, or use 0, and the largest value ranks first.
What happens with ties?
Tied values share a rank and the next rank is skipped, so two values in second place are followed by fourth. RANK.AVG gives tied values the average of their places instead.
Should I use RANK or RANK.EQ?
They give the same result. RANK.EQ is the newer name; RANK is kept so older files keep working.
Related functions
LARGE — LARGE returns the nth biggest value in a range: the second largest, the third, whichever position you ask for.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
