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

Related Function:

RANK.EQ

Function Description

The Excel RANK.AVG function returns the statistical rank of a given value, within a supplied array of values. If there are duplicate values in the list, the average rank is returned.

The Rank.Avg function is new in Excel 2010, and so is not available in earlier versions of Excel.

The syntax of the function is:

RANK.AVG( 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.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.Avg Function Examples

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

Formulas:

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

Results:

	A	B
1	1	5
2	11	3
3	8	2.5
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.Avg 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.Avg 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.Avg function returns the rank 2.5 for the value 9 (see the example in cell B3), and the next value, 8, has rank 4 (see cell B4).

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

Rank.Avg Function Error

If you get an error from the Excel Rank.Avg 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.Avg 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