The rank of a number within a list: 1 for the largest by default, or the smallest with a non-zero order argument. Ties share the same rank and the next rank is skipped.

Syntax

=RANK.EQ(number, ref, [order])

• Tied values

  =RANK.EQ(85,{70,85,90,85}) → 2

  Two scores of 85 both rank 2; nothing ranks 3.

• Ascending order

  =RANK.EQ(70,{70,85,90,85},1) → 1

  Ascending: the smallest is rank 1.

• Rank a column

  =RANK.EQ(B2,$B$2:$B$20) → 7

  Copied down, ranks every row against the whole column.

• Order 0 or omitted is descending (largest is 1); 1 is ascending.
• Break ties by adding a tiny secondary key: =RANK.EQ(B2,$B$2:$B$20)+COUNTIF($B$2:B2,B2)-1.
• RANK.AVG gives tied values the average of the ranks they span.
• Lock the reference range with $ before copying.

• #N/A when the number is not in the list.
• Skipped ranks after ties surprise people expecting 1, 2, 3.