RANK Function in Excel returns the statistical rank of a given value, within a supplied array of values. If there are duplicate values in the list, these are given the same rank.
Syntax of RANK Function in Excel
- 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 Function in Excel in ascending order:
If the [order] argument is 1 then the rank is calculated in ascending order.
- If the numbers are same then the same rank is allocated to them. In our example the value 1320 is found twice and ranked 5th for both the occurrence .Rank 6 is left unused because we have given Rank 5 twice.
Example of Rank Function in Excel in descending order:
If the [order] argument is 0 then the rank is calculated in descending order.
In Rank Function the values are given same rank in case of tie as shown above.
Example of Rank Function: Return the average rank in case of tie (Ranked in descending order):
In order to return the average of rank in case of tie in values, use RANK.AVG Function.
- If the numbers are same then the average of rank is allocated to them. In our example the value 1320 is found twice and ranked 5.5 for both the occurrence .Rank 6 is left unused because we have given Rank 5.5 twice.
- The [order] argument is 0, so the rank is calculated in descending order.
Example of Rank Function: Return the average rank in case of tie (Ranked in ascending order):
In the below example we used RANK.AVG Function which returns the average rank when same values are encountered and the values are Ranked in Ascending order.
- The [order] argument is 1, so the rank is calculated in ascending order.
