The following article introduces you to the RANK.AVG function - one of the functions in the group of statistical functions that is very popular in Excel.
Description: The function returns the rank of a number in a list of numbers whose size is in correlation with other values. If multiple values have the same rank -> the function returns the average rank. Support functions from Execl 2010 version onwards.
Syntax: RANK.AVG (number, ref, [order])
Inside:
- number : The value you want to find rank, is a required parameter.
- ref: Array or reference to a list of numbers, is a required parameter.
- order: The number indicates the order of the rank, an optional parameter.
Attention:
- If order is omitted -> the default function sorts the terms in descending order.
- If order ≠ 0 -> the default function sorts the terms in ascending order.
For example:
Determine the rank of the value 66 in the data array described in the following data table:
- Determine rank of 66 value in descending order. In a cell to calculate, enter the formula : = RANK.AVG (D6, D7: J7, D8)
- Press Enter -> rank of the value 66 is:
- Determine rank of 66 value in ascending order. In the cell to be calculated enter the formula : = RANK.AVG (D6, D7: J7, D8) -> Press Enter -> the return value is:
So with 2 different arrangements -> rankings of the same value are different.
Thus the value order ≠ 0 -> array are arranged in ascending order.
- In case there are 2 duplicate values with the same rank -> the function returns the average rank:
Above are instructions and some specific examples when using the RANK.AVG function in Excel.
Good luck!