Rank the Values using Rank Function in Excel

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

RANK( 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 Function in Excel in ascending order:

If the [order] argument is 1 then the rank is calculated in ascending order.

RANK FUNCTION IN EXCEL 1

  • 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.

RANK FUNCTION IN EXCEL 2

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.

RANK FUNCTION IN EXCEL 3

  • 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.

RANK FUNCTION IN EXCEL 4

  • The [order] argument is 1, so the rank is calculated in ascending order.

previous-small Rank function in Excel                                                                                                                next_small rank function in Excel