The RANK.EQ function is a Statistical formula that returns the rank of a given value, within the specified array of values. If there is more than one values that share the same rank, the higher rank of that set of values is going to be returned by the formula. In this guide, we’re going to show you how to use the RANK.EQ (RANK EQ Excel) function and also go over some tips and error handling methods.

Supported versions

  • Excel 2010 and newer versions

RANK EQ Excel Function Syntax

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

Arguments

number The number for which you want to find the rank.
ref An array of, or a reference to a list of numbers to rank against.
[order]

Optional. The order of the ranking.

  • 0: Descending order (default)
  • 1: Ascending order

RANK EQ Excel Function Examples

The RANK.EQ Excel function returns the mathematical placement of a number in a given data set. The order can be controlled by using the third optional argument. If you would like the largest number to come first, use zero (0) for the optional argument. Otherwise, using one (1) ensures that the smallest number comes first. Let’s see both scenarios in examples.

Descending order

In the following example, you will see that the largest number in the sample data set (9) gets the 1st rank. Since there are two 9 values in the list, RANK.EQ Excel function returns 1 for each. The counter continues from 3.

=RANK.EQ($B7,$B$7:$B$15,0)

RANK.EQ 01

Ascending Order

Enter 1 into the [order] argument to get rankings in ascending order. This time, the largest values (two 9s) share the last place, 5.

=RANK.EQ($B20,$B$7:$B$15,1)

RANK.EQ 02

Download Workbook

Tips and Remarks

  • RANK.EQ Excel function is the updated version of the original RANK function. If you are using an older Excel version, use the RANK function instead.
  • RANK.EQ function returns the higher rank if there are multiple values that would be placed at the same rank. Use the RANK.AVG to return the average rank.
  • The function ignores non-numerical values in the ref argument.

Errors

  • If a number isn’t found in the ref argument, the RANK.EQ function returns the #N/A error value.