Updated June 8, 2023
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, and the rank of your employees? Finding rank isn’t the hardest task, and if there are no duplicate values, then the problem arises. In the corporate world, we often stress an employee’s rank among his group or overall rank at the company level. Regarding the educational sector, rank is not just a number but pride, prestige, and life for somebody.
This article will discuss finding an Excel Formula For Rank for your data. We can find Excel Formula For Rank in several ways, and we will explore each in today’s article.
Steps to Find Largest to Smallest Rank
We can also find the rank of teams by using the sorting option. 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 the IPL team’s 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, select the option Points.
Step 4: 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: Insert serial numbers from 1 to 8 in the Rank column.
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 how to use 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 a series of numbers.
RANK Formula in Excel
Below is the RANK Formula in Excel:
RANK.EQ formula in Excel has 3 arguments; out of those 3, two are mandatory arguments, and one is optional.
- Number: This is the number you are trying to find the rank among many other values.
- Ref: This is a range of numbers. We are trying to find the rank for NUMBER out of this range of numbers.
- [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.
Let’s apply this formula to find the rank for 8 IPL teams, i.e., =RANK.EQ (B2, $B$2:$B$9)
Here RANK. The 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. The EQ function has returned the same ranking for duplicate numbers. We can overcome this limitation by using the 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. The 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 a result, COUNTIF returns 1, and overall, our value will be 5.
Look at cell D6 by the time formula reaches cell D6, RANK.EQ returned 7 as a result. COUNTIF returned 2 as the result because COUNTIF found 2 counts of number 14 by the time it reached 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 RANK replaces it.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 the next ranking slot. For example: If two numbers are supposed to rank as 14 & 15, RANK.EQ function will return as 14 for both the values by ignoring the next rank slot, 15.
- If you want to slot ranks in ascending order, 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
