Maruf Islam
Rank Excel formula with duplicates is one of the ways to sort data in ascending or descending orders. Excel normally ranks duplicate entries as the same number as it’s identical. However, in this article, we demonstrate ways to rank duplicates with different ranks depending on where it sits.
Let’s say, we have a dataset consisting of Student Name, Student ID, and their Obtained Number in a particular subject (i.e., Math). We want to rank them depending on obtained marks. However, the ascending or descending rank prioritizes earlier Student IDs and considers duplicates as different.
Download Excel Workbook
3 Examples of Using Formula to Rank with Duplicates in Excel
Method 1: Rank Duplicates by Using RANK and COUNTIF Functions
Excel offers RANK or RANK.EQ function to normally rank values without considering duplicates. As a result, the RANK or RANK.EQ function ranks duplicates by the same number in a dataset. The syntax of the RANK function is
RANK(number,ref,[order])The arguments define
number; value which rank you want to find
ref; array, list, or range of numbers you want the number to rank against.
[order]); how to rank the number against the ref. [Optional]
0 – or default rank numbers in descending order (i.e., largest to smallest)
1 – rank numbers in ascending order (i.e., smallest to largest)
🔄 Now, if we use the following formula to rank the numbers in our dataset.
=RANK(D5,$D$5:$D$14,0)Comparing the assigned values with arguments
D5=number
$D$5:$D$14=ref
0=descending order
We get a similar scenario like the following picture
After inspecting the outcomes, we can argue that the RANK function ranks the duplicate values as the same. But it then counts the next values’ rank with latter numbers (considering how many duplicates are present in the range). For example, the function goes from rank 1 to 3 as there is a duplicate rank 1 and the function masks it as rank 2. Similarly, the RANK function does the same with the latter ranks.
🔄 We can achieve slightly different rank types using a combined function such as the following
=SUM(--(IF(FREQUENCY(D$5:D$14,D$5:D$14),D$5:D$14>D5)))+1The formula consists of the FREQUENCY, IF, and SUM functions to insert rank against an array. And the formula ranks the duplicates values with the same rank number without masking any numbers.
We can see from the outcomes that the combined formula ranks duplicate values the same and all the other numbers explicitly.
Since, we know, ranking duplicate values at a separate rank can’t be done with a simple RANK function we use combined RANK and COUNTIF functions to achieve the job.
1.1. With Descending Order
➤ To rank duplicates along with the other values, use the below formula in any blank cell (i.e., E5)
=RANK(D5,$D$5:$D$14,0)+COUNTIF($D$5:D5,D5)-1In the formula, the RANK function has all its arguments as explained earlier in the section. And COUNTIF function has a range (i.e., $D$5:D5 ), criteria (i.e., D5). 1 is deducted from the COUNTIF result to ensure the ranking begins with 1. Otherwise, the formula returns rankings without rank 1.
➤ Press ENTER and Drag the Fill Handle to display the ranks in descending order as depicted in the latter image.
1.2. With Ascending Order
➤ Similar to the descending order, paste the below formula in any adjacent cell (i.e., E5).
=RANK(D5,$D$5:$D$14,1)+COUNTIF($D$5:D5,D5)-1All the arguments inside the formula assign the same values as it does in 1.1. Descending Order section.
➤ Hit ENTER and Drag the Fill Handle to appear the ranks smallest to largest as depicted in the below picture.
Read More: How to Create an Auto Ranking Table in Excel (with Quick Steps)
Similar Readings
- How to Rank with Ties in Excel (5 Simple Ways)
- Calculate Rank Percentile in Excel (7 Suitable Examples)
- Rank IF Formula in Excel (5 Examples)
- How to Calculate Top 10 Percent in Excel (4 Easy Ways)
Method 2: Combining RANK and COUNTIFS Functions (with Criteria)
Our dataset has only one obtained marks column to deal with. There may be an instance that arises where we consider ranking our marks not only obtained marks in a particular subject but also another condition (i.e., Total Number). Then we want to rank students by assigning two criteria one is the obtained marks in math and the other is the total obtained number.
For that reason, we modify our dataset by inserting an extra column containing the Total Number as shown in the below picture. We are aware of the fact that the obtained numbers in the particular subject are repetitive but total numbers aren’t. And that’s how we can simply rank the duplicates.
Step 1: Write the following formula in a cell (i.e., F5) beside your raw dataset.
=RANK($D5,$D$5:$D$14)+COUNTIFS($D$5:$D$14,$D5,$E$5:$E$14,">"&$E5)An additional criterion is added through the COUNTIFS function. The additional criterion is assigning the total number range (i.e., $E$5:$E$14) greater than the first entry (i.e., $E5). The formula compares all the entries with themselves and ranks them accordingly.
Step 2: Use the ENTER key to apply the formula afterward Drag the Fill Handle to do as such to the whole range. In a moment all the cells get ranked as shown in the below picture.
Read More: How to Rank Within Group in Excel (3 Methods)
Method 3: Merging IF, RANK and COUNTIF Functions
In the previous methods, we use composite functions to create a formula to deal with the ranking. In this method, we apply a similar approach by combining three functions to create a workable formula of our own.
We run a logical_test using the IF function and insert values depending on the logical _test returns values. The syntax of the IF function is
=IF (logical_test, [value_if_true], [value_if_false])And our about to use formula is
=IF($D5="","",RANK($D5,$D$5:$D$14,0)+COUNTIF($D$5:$D5,$D5)-1)$D5=””, is the logical_test run by the formula and if the test returns TRUE, the formula inserts a blank in the formula cell (i.e., E5). Otherwise displays the rank as we instructed to do in the RANK($D5,$D$5:$D$14,0)+COUNTIF($D$5:$D5,$D5)-1 portion of the formula. The [value_if_false] formula portion defines all the arguments similarly as it does in the earlier section.
3.1. With Descending Order
➤ Type the following formula in any blank cell (i.e., E5).
=IF($D5="","",RANK($D5,$D$5:$D$14,0)+COUNTIF($D$5:$D5,$D5)-1)
➤ After typing the formula Press ENTER then Drag the Fill Handle to make the ranks emerge as depicted in the below screenshot.
3.2. With Ascending Order
Identical to the descending order, follow the below sequences to rank the numbers in ascending order.
➤ Insert the below formula in one of the cells (i.e., E5).
=IF($D5="","",RANK($D5,$D$5:$D$14,1)+COUNTIF($D$5:$D5,$D5)-1)
➤ Since you insert the formula, hit the ENTER key to apply the formula for the entries. Drag the Fill Handle in order to rank all the entries in the dataset.
🔄 Sometimes, we have absent students who don’t appear in the exams. As a result, their obtained number of cells remains empty. These empty cells pose various error issues.
For instance, we have some blank entries against the numbers as shown in the following dataset. We want the formula to ignore those blank cells and return the ranks.
➤ Modify the IF, RANK, and COUNTIF formula as below and insert in any cell (i.e., E5)
=IFERROR(RANK($D7,$D$5:$D$14,1)+COUNTIF($D$5:$D7,$D7)-1,"")The IFERROR formula displays blank cells in entries where empty cells are referred to, otherwise inserts the ranks.
➤ Apply the ENTER key then Drag the Fill Handle to bring out all the ranks.
Read More: Ranking Data in Excel with Sorting (3 Quick Methods)
Conclusion
In this article, we demonstrate ways to rank Excel formula with duplicates. However, not many ways are there to achieve such ranking. We use the combination of RANK, COUNTIF, and IF functions to create workable formulas and apply them to fulfill the conditions. Hope these described methods quench your quest. Comment, if you have further queries or have something to add.
Rank in excel
I think it injustice if 3 students obtained same marts and 3 different ranks.
If ranks same then the next two ranks are absent
Hello Jamil Khan,
Thank you for your question. Actually, this is how the RANK function works, that is to say, it ranks the duplicate values in ascending or descending order according to the given argument. Now, to have the same ranks for identical values you can follow Method 1 or download the Excel file that the ExcelDemy team has created.
Download the Excel File below.
Ranking Duplicates.xlsx