Return the rank of a number in a data set
=RANK.AVG(number,ref,[order])
RANK.AVG and RANK.EQ functions are replacements for the compatibility function RANK available in earlier versions of Excel.
Return the rank of a numerical value in a numeric data set. The rank of a number is its position relative to other values in a list. If more than one value has the same rank, the average rank is returned.
RANK.AVG syntax requires the number to be ranked, the data set against which to compare the number and (optionally) the order of ranking (ascending or descending).
RANK.AVG examples.
In the examples below, the RANK.AVG function is used to rank population numbers (one a defined number, the other a cell reference) in both ascending and descending order against a list of other country populations. The second table ranks the number of days per month, where the rank of similar values are averaged.
Use the RANK.AVG function to return the rank of a number in a data set.
Syntax
RANK.AVG (number,ref,[order])
The RANK.AVG function consists of at least 2 arguments:
- Number. Required. The number to be ranked.
- Ref. Required. A list of numbers (array or range reference) against which to rank the number.
- Order. Optional. A zero (0) to rank in descending order or a one (1) to rank in ascending order.
Try it now!
- Enter your formulas into the grey cells.
Scope
RANK.AVG can only be applied to numerical values. Nonnumeric values in the Ref argument are ignored
|
=RANK.AVG (1,C2:C6,0) || result is 5 (descending order) where C2:C6 contain the numbers 1,2,3,4,5 |
|
=RANK.AVG (C2,C2:C6,1) || result is 1 (ascending order) where C2:C6 contain the numbers 1,2,3,4,5 |
|
=RANK.AVG (2,C2:C6,1) || result is 2.5 (ascending order, the average of position 2 and 3) where C2:C6 contain the numbers 1,2,2,4,5 |
Caveats
- order = 0: number is ranked in descending order (default). The largest number will return rank #1.
- If order is 0 (zero) or omitted, Excel ranks number as if ref were a list sorted in descending order.
- order = 1 or any nonzero value: number is ranked in ascending order. The smallest number will return rank #1.
- If order is any non-zero value, Excel ranks number as if ref were a list sorted in ascending order.
- If the ref list contains duplicates, RANK.AVG will average the rank of the duplicates.
- For example, the descending ranking of 1,2,2,4,5 would be 5,3.5,3.5,2,1.
- Numbers do not need to be sorted prior to using the RANK.AVG function
Related Functions
- RANK returns the rank of a number in a data set (in earlier versions of Excel).
- RANK.EQ() returns the rank of a number in a data set.
Functions Category