Excel Formula For Rank (Table of Contents)
Introduction to Excel Formula For Rank
How do you find the rank of students, the rank of teams, rank of your employees? Finding rank isn’t the hardest task, and if there are no duplicate values then the problem arises. Often in the corporate world, we stress on the rank of an employee among his group or overall rank at the company level. When it comes to educational sector rank is not just a number it is pride, prestige, and life for somebody.
In this article, we will discuss the concept of finding Excel Formula For Rank for your data. There are several ways we can find Excel Formula For Rank and we will explore each one of them in today’s article.
Steps to Find Largest to Smallest Rank
We can find the rank of teams by using sorting option as well. Sorting is nothing but arranging things in order like Smallest to Largest, Largest to Smallest and using this same concept we can draw ranks.
Now we will find Rank using Sort Largest to Smallest with the below steps. Let’s consider the below data of IPL teams table points.
Step 1: Select the entire data range. Go to Data > Sort
Or else you can press ALT + D + S (Do not hold any key type one at a time).
Step 2: Now you will see the below dialog box.
Step 3: Now under Sort by select the option Points.
Step 4: Now under Order select ” Largest to Smallest ”
Step 5: Click on OK to find the result.
Step 6: The Output will be as shown below.
Step 7: In Rank column insert serial numbers from 1 to 8.
This is not the traditional way of finding Rank in excel. We have a built-in function called RANK.EQ to find Ranks. I have shown just to tell you the ways of using excel tools as an alternative to one another.
Find Rank Using RANK.EQ Function in Excel
Microsoft has a built-in function called RANK.EQ function which can find the rank of a number among series of numbers.
RANK Formula in Excel
Below is the RANK Formula in Excel:
RANK.EQ formula in excel has 3 arguments and out of those 3 two are mandatory arguments and one is an optional argument.
- Number: This is the number you are trying to find the rank among many other values.
- Ref: This is a range of numbers. Out of this range of numbers, we are trying to find the rank for NUMBER.
- [Order]: This is an optional argument. Here you can specify the ranking of your number whether it is in Ascending or Descending order. Zero (0) is for descending and One (1) is for ascending order.
Ok, let’s apply this formula to find the rank for 8 IPL teams i.e. =RANK.EQ (B2, $B$2:$B$9)
Here RANK.EQ function returned the same ranking for the teams RR & DC. The RANK function returns the same rank for duplicate values and ignores the subsequent ranking number.
Find RANK for Duplicates in Excel
We have seen RANK.EQ function has returned the same ranking for duplicate numbers. We can overcome this limitation by using COUNTIF function with RANK.EQ function. The formula used here is =RANK.EQ (B2, $B$2:$B$9) +COUNTIF ($B$2:B2, B2)-1
Firstly, RANK.EQ function returns the rank as usual. Then COUNTIF function counts the current cell value in the current range and deducts 1 from the result. In the first case RANK.EQ function returns 5 as result, COUNTIF returns 1 as the result and overall we value will be 5 itself.
Now, look at the cell D6, by the time formula reaches cell D6, RANK.EQ returned 7 as the result. COUNTIF returned 2 as the result because COUNTIF found 2 counts of number 14 by the time it reaches the cell D6 and as usual, one will be deducted from 2 and the RANK will be placed as 8.
Things to Remember About Excel Formula For Rank
- Do not use the RANK function because it is replaced by RANK.EQ in 2010 and later versions.
- Sorting and finding the ranking is the non-traditional way of finding the rank in excel.
- EQ returns the same ranking for duplicate values by ignoring very next ranking slot. For example: If two numbers supposed to rank as 14 & 15 RANK.EQ function will return as 14 for both the values by ignoring the very next rank slot 15.
- Just in case if you want to slot ranks in ascending order you need to specify the order as 1. By default, it takes zero as the argument.
Recommended Articles
This is a guide to Excel Formula For Rank. Here we discuss how to use autofit in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
