Related Function:

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

  • This function was introduced in Excel 2010 and so is not available in earlier versions.

Syntax

=RANK.AVG(number,ref,[order])

Arguments

Argument Description
number The number whose rank you want to find
ref An array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored
[order] Optional. Defines whether the ref list should be ordered in ascending or decending order

  0 Ranks number as if ref were a list sorted in descending order
  1 Ranks number as if ref were a list sorted in ascending order

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

Examples

  A B C D E
1 Data   Formula Result Notes
2 9   =RANK.AVG(9,A2:A8) 6 Finds the rank, or position, of the value 9 in the cell range A2:A8 with ref sorted in descending order
3 37   =RANK.AVG(9,A2:A8,1) 2 Finds the rank, or position, of the value 9 in the cell range A2:A8 with ref sorted in ascending order
4 20        
5 6        
6 16        
7 26        
8 32        

Note:  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.

Common Function Error(s)

Problem What went wrong
#N/A Occurs if the supplied number is not present in the supplied array of values