Excel RANK Function (Table of Contents)
RANK in Excel
Rank function in excel is used for finding out the best sequence position of any selected cell from the given hierarchy or range, which is only applicable for number. And it is because Rank can only be measured in numbers. If we have 5 number and want to find the rank (or position) of any number, we simply need to select the range and then choose the order in which rank we want.
RANK Formula in Excel:
Below is the RANK Formula in Excel:
Explanation of RANK Function in Excel
RANK Formula in Excel includes two mandatory arguments and one optional argument.
- Number: This is the value or number we want to find the rank.
- Ref: This is the list of numbers in a range or in an array you want to your “Number” compared to.
- [Order]: Whether you want your ranking in Ascending or Descending order. Type 0 for descending and type 1 for ascending order.
Ranking products, people, or services can help you to compare one against another. The best thing is we can see which is at the top, which is at the average level, and which is at the bottom.
We can analyze each one of them based on the rank given. If the product or service is at the bottom level, we can study that particular product or service and find the root cause for that product or service poor performance and take necessary action against them.
Three Different Types of RANK Functions in Excel
If you start typing the RANK function in excel, it will show you 3 types of RANK functions.
- RANK.AVG
- RANK.EQ
- RANK
In Excel 2007 and earlier versions, and only the RANK Function was available. However, later on, a RANK Function has been replaced by a RANK.AVG and RANK.EQ functions.
Though RANK Function still works in recent versions, it may not be available in future versions.
How to Use RANK Function in Excel?
RANK Function in Excel is very simple and easy to use. Let understand the working of RANK Function in Excel by some RANK Formula example.
Example #1
I have a total of 12 teams that participated in the Kabaddi tournament recently. I have team names, their total points in the first two columns.
I have to rank each team when I compared it to other teams on the list.
Since RANK works only for compatibility in earlier versions, I am using RANK.EQ function instead of a RANK Function here.
Note: Both work exactly the same way.
Apply the RANK.EQ function in cell C2.
So the output will be :
We can drag the formula by using Ctrl + D or double click on the right corner of the cell C2. So the result would be:
Note: I have not mentioned the order reference. Therefore, excel by default ranks in descending order.
- =RANK.EQ (B2, $B$2:$B$13) returned a number (rank) of 12. In this list, I have a total of 12 teams. This team scored 12 points, which is the lowest among all the 12 teams we have taken into consideration. Therefore, the formula ranked it as 12, i.e. the last rank.
- =RANK.EQ (B3, $B$2:$B$13) returned a number (rank) of 1. This team scored 105 points, which is the highest among all the 12 teams we have taken into consideration. Therefore, the formula ranked it as 1, i.e. first rank.
This is how RANK or RANK.EQ function helps us find out each team’s rank when we compared against each other in the same group.
Example #2
One common problem with RANK.EQ function is if there are two same values, then it gives the same ranking to both the values.
Consider the below data for this example. I have a batsmen name and their career average data.
Apply the RANK.EQ function in cell C2, and the formula should like the below one.
=RANK.EQ(B2,$B$2:$B$6)
So the output will be :
We can drag the formula by using Ctrl + D or double click on the right corner of the cell C2. So the result would be:
If I apply a RANK formula to this data, both Sachin and Dravid get the rank 1.
My point is if the formula finds two duplicate values, then it has to show 1 for the first-ever value is found and the next value to the other number.
There are many ways we can find the unique ranks in these cases. In this example, I am using RANK.EQ with COUNTIF function.
So the output will be :
We can drag the formula by using Ctrl + D or double click on the right corner of the cell D2. So the result would be:
The formula I have used here is
=RANK.EQ (B2, $B$2:$B$6) this will find the rank for this set.
COUNTIF ($B$2:B2, B2) – 1. COUNTIF formula will do the magic here. For the first cell, I have mentioned $B$2:B2 means at this range, what is the total count of the B2 value then deduct that value from 1.
The first RANK returns 1 and COUNTIF returns 1, but since we mentioned -1, it becomes zero; therefore, 1+0 = 1. For Sachin, RANK remains 1.
For Dravid, we got the Rank as 2. Here RANK returns 1 but COUNTIF returns 2 but since we mentioned -1 it becomes 1 therefore 1 + 1 = 2. The rank for Dravid is 2, not 1.
This is how we can get unique ranks in case of duplicate values.
Things to Remember
- A RANK Function is replaced by RANK.EQ in 2010 and later versions.
- A RANK Function in Excel can accept only numerical values. Anything other than numerical values, we will get an error as #VALUE!
- If the number you are testing is not present in the list of numbers, we will get #N/A! Error.
- The RANK function in Excel gives the same ranking in the case of duplicate values. Anyhow, we can get unique ranks by using the COUNTIF function.
- Data need not sort in ascending or descending order to get the results.
Recommended Articles
This has been a guide to RANK in Excel. Here we discuss the RANK Formula in Excel and How to use a RANK Function in Exel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
