Author: Oscar Cronquist Article last updated on October 01, 2018

The RANK.EQ function calculates the rank of a number in a list of numbers, based on its position if the list were sorted.

Formula in cell C7:

=RANK.EQ(C3,B7:B13,C4)

The image below shows the position of number 10 if the list were sorted in an descending order.

This list sorted in a descending order shows number 10 in position 6 which is the rank the RANK.EQ function calculates in cell F2 in the top image. Note that the last duplicate (10) also gets rank 6 and not 7 as the image above shows.

Excel Function Syntax

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

Arguments

number Required.
ref Required. A list of numbers.
[order] Optional. This argument determines how the RANK.EQ function ranks a number.

0 (zero) - Default value, list in argument ref is sorted in a descending order.

1  - List in argument ref is sorted in an ascending order.

Comments

RANK.EQ function ignores non-numeric values in ref argument.

The RANK.EQ function calculates the same rank to duplicate identical numbers, however, it also moves the ranks for the following numbers. This example calculates ranks based on a list sorted in an ascending order.

Formula in cell C3:

=RANK.EQ(B3,$B$3:$B$9,1)

10 has a duplicate number, both numbers get the same rank. The following number is 21, that number gets rank 3. No number has rank 2.

If you want the to give duplicate numbers a unique rank use the following formula:

=RANK.EQ(B3,$B$3:$B$9,1)+COUNTIF($B$3:B3,B3)-1