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:


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).