The Excel RANK.EQ Function

Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Rank.Eq-Function

Search this site:

Custom Search

Related Function:

RANK.AVG

Statistical Rank

If you have a list of numbers, the statistical rank tells you the order of a value in that list.

The rank is therefore calculated by placing the numbers in order and then assigning an order to each value in the list.

For example, in the list 6, 2, 4, the rank of each individual list value is :

6	-	rank = 3
2	-	rank = 1
4	-	rank = 2

Basic Description

The Excel RANK.EQ function returns the statistical rank of a given value, within a supplied array of values. If there are duplicate values in the list, these are given the same rank.

The Rank.Eq function is new in Excel 2010, and so is not available in earlier versions of Excel. However, the function is simply a renamed version of the Rank function that is available in earlier versions of Excel.

The format of the function is :

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

Where the function arguments are:

number

The value for which you want to find the rank

ref

An array of values containing the supplied number

[order]

An optional argument which defines whether the ref list should be ordered in ascending or decending order
The [order] argument can take the value 0 or 1, meaning :

0	-	denotes decending order
1	-	denotes ascending order

If the [order] argument is omitted, it will take the default value of 0 (ie. decending order). Any non-zero value is treated as the value 1 (ie. ascending order)

RANK.EQ & RANK.AVG Functions

The Rank.Eq and Rank.Avg functions are both new to Excel 2010. The difference between these two functions occurs when there are duplicates in the list of values. The Rank.Eq function returns the lower rank, whereas the Rank.Avg function returns the average rank.

For example, in the list of values 4, 5, 5, 6 (in ascending order), the value '5' occupies the 2nd and 3rd positions. Therefore, when calculating the rank of the value 5 :

-	RANK.EQ returns the rank 2
-	RANK.AVG returns the rank 2.5

Both the Rank.Eq and the Rank.Avg function return a rank of 4 for the value 6.

Rank.Eq Function Examples

The following spreadsheet shows examples of the Excel Rank.Eq Function used to calculate the rank of values within the simple set {1, 11, 6, 9, 2, 5, 9}.

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right.

Formulas:

Examples of use of the Excel Rank.Eq Function

Results:

Note that, in the above examples:

the functions in cells B1 and B2 are the same - The order argument is omitted in cell B1 and so it takes the default value of 0 (descending order)
as the supplied array contains two values equal to 9, when the array is in ascending order, the number 9 has rank 5 (see the example in cell B4). However, as there are two 9's in the array, these account for positions 5 and 6. Therefore the next value, 11, has rank 7 (see cell B5)

Further information and examples of the Excel Rank.Eq function can be found on the Microsoft Office website.

Trouble Shooting

If you get an error from the Excel Rank.Eq function this is likely to be the #N/A error :

Common Error

#N/A

Occurs if the supplied number is not present in the supplied array of values.

(Note that the function does not recognise text representations of numbers as numeric values, so you will also get the #N/A error if the values in the supplied 'ref' array are text values)

Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Disclaimer

Privacy Policy