RANK.AVG Function in Excel calculates and returns the statistical rank of a given value. When the value occurs multiple time then average of the rank is allocated to that value. i.e. in case of tie average of rank is allocated to that value
Syntax of RANK.AVG Function in Excel
RANK.AVG( number, ref, [order] )
- number – The value for which you want to find the rank.
- ref – An array of values containing the supplied number.
- [order] – order of the rank i.e. ascending or descending order. The [order] argument can have the value 0 or 1.
- If 0 – ranked in descending order
- If 1 – ranked in ascending order
Example of RANK.AVG Function in Excel in descending order:
- If the numbers are same then the average of rank is allocated to them. In our example the value 1020 is found twice and rank 8.5 is allocated for both the occurrence .Rank 9 is left unused because we have given Rank 8.5 twice.
- If the [order] argument is 0 then the rank is calculated in descending order.
Example of RANK.AVG Function in Excel in ascending order:
- If the [order] argument is 1 then the rank is calculated in ascending order.
- Here, 1020 is found twice and ascending order rank of 2.5 is allocated for both the occurrences.