Excel RANK.EQ function

The RANK.EQ function returns the rank of a number compared to other numbers in the same list. The same rank will be assigned to duplicate values in the list.

RANK.EQ function VS. RANK.AVG function

Both the RANK.EG function and the RANK.AVG function can be used to return the rank of a number in a list of numbers. The difference between them is that the RANK.EQ function assigns the top rank to duplicate values, while the RANK.AVG function assigns the average rank to duplicate values.

Note: This function was first introduced in Excel 2010 to replace the RANK function. It is available in Excel 2010 and later versions.

Syntax

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

Arguments

Remarks

Return value

It returns a numeric value.

Example

There is a class report card as shown in the screenshot below. To calculate the rank of each score compared to other scores within the report, you can apply the RANK.EQ function to get it done.

Select a cell (says D6 in this case) next to the first score, enter the formula below and press Enter to get the rank of the first score in the list. Select this result cell and drag its AutoFill Handle down to get ranking of other scores.

=RANK.EQ(C6,$C$6:$C$18,0)

Notes:

1) In this case, the RANK.EQ function assigns the same rank value (6) to the number 77 that appears twice in the list. Meanwhile, you can see that none of the numbers have a rank of 7. That is because the presence of the duplicate values affects the ranks of subsequent numbers.

2) The formula above ranks the scores in descending order. To rank the scores in ascending order, you can apply the following formula:

=RANK.EQ(C6,$C$6:$C$18,1)

Related Functions