The RANK.EQ function
The rank of a value in a list: first, second… The best salesperson, the biggest expense.
Syntax
=RANK.EQ(number, ref, [order])
In a French Excel: =EQUATION.RANG(nombre; référence; [ordre]).
Arguments
- number: the value to rank.
- ref: all the values, locked with
$. - order: 0 or omitted, the largest is first; 1, the smallest.
An example
=RANK.EQ(C2,$C$2:$C$50)
The rank of C2's revenue among the 49 salespeople.
Pitfalls
- An unlocked range slides when copied and skews the ranks.
- Ties share the same rank, and the next rank is skipped (1, 2, 2, 4).
Going further
- LARGE, for the value of a given rank.
- Find the right formula: "rank a value in a list".
Frequently asked questions
- RANK.EQ or RANK?
- RANK is the older version, identical; RANK.AVG gives ties the average of their ranks.