RANK Formula Generator
Rank a number against a list of values, largest first or smallest first.
RANK.EQ
Rank a number against a list of values, largest first or smallest first.
The cell whose position you want, usually the value on the current row. It has to be a real number, and it has to appear somewhere in the reference range.
The full list to rank against. Use dollar signs so the range does not shift when you fill the formula down.
0 ranks largest first, so the highest value is rank 1. Use 1 to rank smallest first. The argument is optional in Excel and defaults to 0.
=RANK.EQ(A2, $A$2:$A$100, 0)Worked Example
Five sales totals in B2:B6 (88, 95, 72, 95, 60), ranking the 88 in B2 highest first.
=RANK.EQ(B2, $B$2:$B$6, 0)Returns: Returns 3. The two 95s tie at rank 1, rank 2 is skipped entirely, so 88 comes out third.
Checks Before You Paste
- •Lock the reference range with dollar signs, as in $A$2:$A$100, before filling down. A relative range slides one row per copy, and every result is wrong in a way that still looks plausible.
- •order 0 means descending, so the biggest number gets rank 1; any non-zero value, conventionally 1, means ascending so the smallest gets rank 1. Leaving the argument off entirely is the same as passing 0.
- •Ties share the higher rank and the next rank is skipped: 95, 95, 88 ranks as 1, 1, 3 with no rank 2. RANK.AVG returns 1.5, 1.5, 3 instead. To force unique ranks use =RANK.EQ(A2, $A$2:$A$100, 0) + COUNTIF($A$2:A2, A2) - 1, where the half-locked range grows as you fill down and breaks ties by row order.