RANK Function in Excel

RANK Function in Excel returns the rank of a number in a list of numbers. you can also use RANK.AVG to return the average rank if it is more than one number.

Rank Function Syntax: =RANK(number,ref,[order])

Arguments Explained:

• number - this is the value for which we need to find the rank.
• ref - it is the range of cells or list.
• order - it sepcifies that how the ranking will be done (if you select 0 this will display results in descending order. if you select 1 this will display results in ascending order.)

How to Use the RANK Function in Excel

In the below example, we have ten product names with total sales. now the question is, how to find which product made more sales and which product made less?

We can also use ascending sort or descending sort to find the most sold product.

But using RANK on Excel it is easy to find most sold or less sold products.

To find the RANK for these products we have used =RANK(B2,$B$2:$B$11,0) formula.

First, use this RANK formula on cell C2. after that drag the RANK formula to all ten products.

Note: when dragging the RANK function to all ten products use an absolute reference ($B$2:$B$11). it freezes the references. an only relative reference (B2) changes to B2, B3, B4, etc to B11.

RANK.AVG Function

In the above RANK function example, if any two product sales having the same number, the RANK function returns the same rank to both numbers.

To avoid this kind of scenario, you can use RANK.AVG function in Excel. it returns the average rank if the multiple numbers have the same rank.

Result is

Note: If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order. If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

Download