The Excel RANK Function

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 arranging the numbers in a specified order (usually descending) and then assigning a position to each value in the list.

For example, for the list 6, 4, 2, the rank of each number is:

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

Function Description

The Excel RANK 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.

In Excel 2010, the Rank function has been replaced by the Rank.Eq function. However, the Rank function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.

The syntax of the Rank function is:

RANK( 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 that defines whether the supplied ref array should be ordered in ascending or descending order.

The [order] argument can have the value 0 or 1, meaning:

0	-	use descending order
1	-	use ascending order

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

Rank Function Examples

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

Formulas:

	A	B
1	1	=RANK( 5, A1:A7 )
2	11	=RANK( 5, A1:A7, 1 )
3	8	=RANK( 9, A1:A7 )
4	9	=RANK( 8, A1:A7 )
5	2
6	5
7	9

Results:

	A	B
1	1	5
2	11	3
3	8	2
4	9	4
5	2
6	5
7	9

Note that, in the above examples:

In cells B1, B3 & B4, the [order] argument is omitted. Therefore, the Rank function uses the descending array, 11, 9, 9, 8, 5, 2, 1.
In cell B2, the [order] argument is equal to 1. Therefore, the Rank function uses the ascending array, 1, 2, 5, 8, 9, 9, 11.
The supplied array contains two values equal to 9, which occupy positions 2 and 3 when the array is ranked in descending order. In this case, the Rank function returns the rank 2 for the value 9 (see the example in cell B3), and the next value, 8, has rank 4 (see cell B4).

For further information and examples of the Excel Rank function, see the Microsoft Office website.

Rank Function Error

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

Common Error

#N/A

Occurs if the supplied number is not present within the supplied ref.

(Note that the Rank 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