RANK Function | Syntax | Example | RANK.AVG
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.
Arguments Explained:
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.
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.