RANK.AVG (RANK AVG) is a Statistical function that can return the rank of a given value, within the specified array of values. If there are values that share the same rank, the average rank of that set of values will be returned. In this guide, we’re going to show you how to use the RANK.AVG function and also throw in some tips and error handling methods.
Supported versions
- Excel 2010 and newer versions
RANK AVG Function Syntax
Arguments
| number | The number for which you want to find the rank. |
| ref | An array of, or a reference to a list of numbers to rank against. |
| [order] |
Optional. The order of the ranking.
|
RANK AVG Function Examples
RANK.AVG function returns the mathematical placement of a number in a given data set. The order of ranking can be controlled by a third, optional argument. If you would like the largest number to come first, use zero (0) for the optional argument. Otherwise, using one (1) ensures that the smallest number will come first. Let's see both scenarios in examples.
Descending order
In the following example, you will see the largest number in the data set (9) gets the rank 1.5. Since there are two 9 values, RANK.AVG function returns 1.5 for each ((1 + 2) / 2). The counter continues from 3.
Ascending Order
Enter 1 to [order] argument to get rankings in ascending order. This time, the largest values (9) shares the average of the last place, 5.5.
Tips and Remarks for the RANK AVG Function
- RANK.AVG (RANK AVG) function is the updated version of the RANK function and has been released with 2010 version.
- RANK.AVG function returns the average rank if there are multiple values that share the same rank. Use the RANK.EQ function instead to return the higher rank.
- The function ignores non-numerical values entered for the ref argument.
Errors
- If number is not existed in ref, RANK.AVG function returns #N/A error value.
