ASM Arman
Sometimes in Excel, we have to work with the kind of data that might need to be ranked or ordered based on a specific set of criteria. For example, when you are trying to determine the positions of students in a class based on the scores they have achieved in multiple subjects or when you are trying to rank the participants of a particular game or competition based on different metrics of performance. While ranking such data, you will often come across the problem of having multiple students or participants taking the same rank or position due to scoring the same number in the exam or all the participants having the same level of performance. In such cases, we need to break the ties in ranking to separate the participants. In this tutorial, I will show you how to rank with ties in Excel.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
5 Suitable Methods to Rank with Ties in Excel
Let’s consider a situation where we have an Excel worksheet that contains information about the participants and their performance in a sporting contest. The Excel worksheet has the Names of the participants, their Scores in the contest, Scores they have achieved, and their Ranks based on the performance. The image below shows the General Ranking with ties between two participants and also the Rank without Ties. We will use this Excel worksheet to learn how to rank with ties in Excel
1. Rank First Value in a Tie the Superior Position
If we just consider only one criterion to rank the data in an Excel worksheet, then we can follow the below steps to rank with ties in Excel
Step 1:
- First, we will try to rank the participants based on their Scores in the contest. For that, we will write the below formula in cell E5.
=RANK(C5,$C$5:$C$14)Formula Explanation:
- The RANK function will take two arguments The first one is the cell value C5 while the second one is an absolute range $C$5:$C$14.
- The RANK function will try to determine the rank or order of the cell value C5 in the absolute range $C$5:$C$14.
Step 2:
- Upon pressing ENTER, we will see that the participant in the first row (C5) has been ranked as 8th.
- We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.
- As all the participants have been ranked now, we will now see that 2 participants named Brad (Cell B6) and Chris (Cell B9) are ranked in the same position (Rank 4th).
Step 3:
- We will now insert the below formula in cell F5 to break the tie in the rank.
=RANK(C5,$C$5:$C$14)+COUNTIF(C$5:C5,C5)-1Formula Explanation:
- COUNTIF function takes the expanding range C$5:C5 as the first argument and the cell value C5 as the second argument. It will try to find out how many times the value in C5 (second argument) appears in the expanding range (first argument).
- The value returned by the COUNTIF function will then be added with the value returned by the RANK function and 1 will be subtracted.
- When this formula will be applied to the first tied rank (Cell E6), the COUNTIF function will return 1 as rank 4 appears only once till that point.
- As the value (1) returned by the COUNTIF function will be added to the value returned by the RANK function while 1 will be subtracted, the RANK of the participant Brad will remain unchanged.
- The value returned by the RANK function = 4
- The value returned by the COUNTIF function = 1
- Value to be subtracted = 1
- So, the formula will return the value = 4 + 1 – 1 = 4
- In the case of the second tied rank (Cell E9), the COUNTIF function will return 2 as rank 4 appears twice till that point.
- As the value (2) returned by the COUNTIF function will be added to the value returned by the RANK function while 1 will be subtracted, the RANK of the participant Chris will be increased to 5.
-
- The value returned by the RANK function = 4
- The value returned by the COUNTIF function = 2
- Value to be subtracted = 1
- So, the formula will return the value = 4 + 2 – 1 = 5
- Upon pressing ENTER, we will see that cell F5 is showing 8 as the rank of the participant in cell B5.
- We will now drag the fill handle of cell F5 to apply the formula to the rest of the cells.
- We will now see that the ranks of all the participants have remained unchanged except for the two participants named Brad and Chris who shared the same rank before (Rank 4th).
- The rank of the preceding participant named Brad remains 4th while the following participant Chris is now ranked 5th.
Read More: Rank IF Formula in Excel (5 Examples)
2. Use Two COUNTIF Functions Together to Break the Tie in a Rank in Excel
Alternatively, we can also use two COUNTIF functions together to rank with ties in Excel. We have to follow the below steps. For this example, we will rank the participants based on the Time they passed to complete the task.
Step 1:
- First, we will rank all the participants based Time they passed to complete the task.
- After ranking all the participants, we will now see that 3 participants named Brad (Cell B6) and Chris (Cell B9), and Tobey (Cell B13) are ranked in the same position (Rank 4th).
- Now, we will write the below formula in cell E5.
=COUNTIF($D$5:$D$14,">"&$D5)+COUNTIF($D$5:D5,D5)Formula Explanation:
- The first COUNTIF function will take the absolute range $D$5:$D$14 as the first argument and the cell value D5 as the second argument. It will use the greater than (>) sign to determine how many values in the absolute range $D$5:$D$14 (first argument) is greater than the cell value D5 (second argument).
- The second COUNTIF function takes the expanding range D$5:D5 as the first argument and the cell value D5 as the second argument. It will try to find out how many times the value in D5 (second argument) appears in the expanding range (first argument).
- The two values returned from the two COUNTIF functions will then be added.
- The first COUNTIF function is basically the RANK function but it will return a value one (1) less than the value returned by the RANK Because it will only return the number of those values greater than the cell D5 while the RANK function will return one (1) more than that number. We all know that that is how we calculate the RANK or ORDER of something!!!!
- The second COUNTIF function is similar to the COUNTIF function used in Method 1.
- So, the sum of the values will be similar to the value returned by the formula in Method 1.
Step 2:
- Upon pressing ENTER, we will see that cell F5 is showing 4 as the rank of the participant in cell B5.
- We will now drag the fill handle of cell F5 to apply the formula to the rest of the cells.
- We will now see that the ranks of all the participants have remained unchanged except for the three participants named Brad, Chris, and Tobey who shared the same rank before (Rank 4th).
- The rank of the first participant named Brad remains in 4th position while the following participant Chris is now ranked 5th and the last participant Tobey holds the 6thposition.
Read More: Excel Formula to Rank with Duplicates (3 Examples)
Similar Readings
- How to Calculate Weighted Ranking in Excel (4 Ways)
- Excel Percentile Rank Inc vs Exc
- How to Create an Auto Ranking Table in Excel (with Quick Steps)
3. Use a Second Criteria to Break the Tie in a Rank in Excel
If we have multiple criteria rather than one like above, then we can use the second one to break the tie in a particular rank. For example, we will use the Time to complete the
Step 1:
- First, we will write the below formula in cell
=IF(COUNTIF($C$5:$C$14,C5)>1,RANK(D5,$D$5:$D$14,1)/100,0)Formula Breakdown:
- The first argument or the logical test for the IF function is COUNTIF($C$5:$C$14,C5)>1. So, the IF function will determine if the number of appearances of the cell value C5 in the absolute range $C$5:$C$14 is greater than 1.
- The second argument for the IF function is what it will return if the logical test is TRUE. We have inserted RANK(D5,$D$5:$D$14,1)/100 as the second argument. So, the RANK function will determine the rank of the participants based on the Time they took to complete the task. The first argument for the RANK function is Time (D5), the second one is the absolute range of the Time column $D$5:$D$14. And the last argument is 1 which tells the RANK function to rank the participants in ascending order. We will divide the return value of the RANK function by 100 to get a decimal value less than 1.
- The third argument for the IF function is what it will return if the logical test is FALSE. We have inserted zero (0) as the third argument.
- Every value in the absolute range $C$5:$C$14 or each value in the Score column except for the value in cell B6 and B9 (Score 73) only appears once. So, the COUNTIF function will return 1 as the number of the appearance of the cell value C5 is 1. Hence, the Logical Test of the IF function will return FALSE and the function itself will return 0.
- But in the case of cells B6 and B9 (Score 73), the number of appearances is 2.
- Hence, the Logical Test of IF function will return TRUE and the function itself will return a decimal number that is the return value of the RANK function (Based on the Time column) divided by 100. The rank for the two cell values will be different this time. So, we will get two different decimal numbers. These two different values will act as the Tie Breaker.
Step 2:
- Upon pressing ENTER, we will see that cell F5 is showing the value zero(0).
- We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.
- Now, we will see that formula has populated each cell in the Tie Breaker column with zero (0). But it omits the two cells that shared the same rank (Rank 4th).
- The preceding cell has the value of 08 while the following cell is populated with a value of 0.01.
Step 3:
- We will now write the below formula in cell G5 under the Final Rank
=SUM(E5,F5)Formula Breakdown:
- The SUM function will sum up the existing rank value (E5) and the second rank value based on the Time needed to complete the task (F5) to determine the Final Rank.
- Upon clicking ENTER, we will get the Final Rank for the first participant.
- We will now drag the fill handle of cell G5 to apply the formula to the rest of the cells.
- Finally, we will get the separate ranks for the two participants as the respective values in the Tie Breaker column have been added to the existing ranks.
Read More: Ranking Based on Multiple Criteria in Excel (4 Cases)
4. Split the Available Winnings Specified For a Ranked Tied Between the Rank Holders
Sometimes while distributing the prize or points in a competition, we might not want to break the tie in a specific rank. Instead, we will try to distribute the prize or points specified for that rank along with succeeding occupied ranks due to this tie among all the participants that hold this same rank. For example, we have specified the prize for each rank. We will now split the prizes specified for the 4th, 5th, and 6th positions among the 3 participants holding the same 4th positions. As we have 3 participants holding the 4th rank or position, another 2 participants are not available to hold the 5th or 6th position.
Step 1:
- First, we will write the below formula in the cell.
=AVERAGE(OFFSET($I$4,E5,0, COUNTIF($E$5:$E$14,E5)))Formula Breakdown:
- OFFSET function takes exactly 4 arguments. The first one is the cell reference. In this case, it is the absolute reference to cell I4 ($I$4). So, the reference will not change when we drag the fill handle
- The second argument is the number of rows down from the cell specified in the first argument (I4). In this example, it is the respective value in the same row of the General Rank (E5:E14). So, for the first cell (cell F5), it is 9 (cell E5). OFFSET will refer to the cell in the Prize column (Column I) that is 9 rows down from cell I4. That cell is I13 and this cell exactly refers to the prize specified for rank 9. As we drag the fill handle to apply the formula to different cells in this column (Column F), the OFFSET function will rightly refer to the prize that is assigned to the respective rank in that row. So, we will get the prize for each participant according to their ranks.
- Third argument refers to the number of columns, to the left or right, the function should refer to. In this case, it is zero (0). So, it will not refer to any left or right column.
- The fourth argument is a COUNTIF function that will find out how many times the value in E5 appears in the expanding range $E$5:$E$14. The value returned by the COUNTIF function will tell the OFFSET function how many rows it should refer to or the height of the reference. In the case of the 3 cells with tied ranks (Cell F6, F9, F12), it will return 3. The COUNTIF function will find the rank 4 in the expanding range 3 times. So, in the case of these 3 cells, the OFFSET function will refer to 3 cells: I8, I9, and I10 as the height of the reference is 3. These 3 cells respectively refer to the prize assigned to the ranks 4, 5, and 6.
- Finally, the AVERAGE function will average the cell values that the OFFSET function will refer to. In the case of the 3 cells with tied ranks, it will average the values in 3 cells: I8, I9. So, it will average the prize money assigned to the ranks 4, 5, and 6. Each participant holding the tied ranks will get this average prize money.
Step 2:
- Now, we will see that the first participant who ranked 9th in this list has got $20. Note that, the prize specified for the 9thposition was $20.
- We will now drag the fill handle of cell F5 to apply the formula to the rest of the cells.
- Finally, we will find out that each of the 3 participants holding the 4thposition has got the average of the 3 prizes specified for the 4th, 5th, and 6th.
Read More: How to Rank Average in Excel (4 Common Scenarios)
5. Use the SUMPRODUCT Function To Prevent Skipping Ranks in Excel
If we do not want to break the tie in a rank. But also want the RANK function to prevent from skipping the succeeding ranks. For example, we have a tie in 5th rank with 3 participants belonging to this rank. But the RANK formula has also skipped the 6thand 7th positions. The 2 participants supposed to hold these ranks now belong to the 5th position. So, we want to have these 2 ranks occupied but without breaking the tie in the 5th position. We can use the below formula to achieve this objective.
Step 1:
- First, we will write the below formula in cell
=SUMPRODUCT((D5<=$D$5:$D$14)/COUNTIF($D$5:$D$14,$D$5:$D$14))The SUMPRODUCT function will multiply and then sum up the values in the $D$5:$D$14 which are greater than or equal to cell value D5 with the values returned by the COUNTIF.
Step 2:
- Upon clicking ENTER, we will see that rank of the first participant in the list has remained unchanged.
- We will now drag the fill handle of cell F5 to apply the formula to the rest of the cells.
- Now we will see that the tie in 5th rank has not been broken. But Excel has not skipped the 6thand 7th positions while ranking the participants.
- We have now 3 participants holding only a single position along with any succeeding rank being unskipped. So, we will now have a total of 8 positions or ranks instead of 10 ranks for 10 participants.
Read More: How to Calculate Rank Percentile in Excel (7 Suitable Examples)
Things to Remember
- All of the methods above have used different built-in functions to rank with ties in Excel. You should have a clear understanding of how the functions work. Otherwise, it will be very hard to use these functions to rank with ties in Excel.
- The article has links to the tutorials on each of the functions we have used. Please visit the pages to learn about these functions.
Conclusion
In this article, we have learned to rank with ties in Excel. I hope from now on you can rank with ties in Excel very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
Thank you for your EXCELlent website!
Wish you can help me with my problem, because I am at a loss…
I’m trying to create a scorecard for 20 players who play 22 games.
I need 24 tie breakers:
– 22 Tie breakers for the positions in which they have finished the game;
– 1 Tie breaker for the number of games they have played;
– 1 Arbitrary tie breaker in case everything is tied.
In case the points are tied, then the best position must be checked.
If also the best positions are tied, then the second best must be checked, etc. (up to 22 games).
Let’s say that after 3 games two players are both equal in points and in positions, then the “Arbitrary tie breaker” comes into play.
In case that after 3 games two players are equal in points and positions, but one player has only played 2 games and the other one has played 3 games, the player who played 3 games must be ranked higher.
The names of the players are in columns E40:E60.
Their points are in GU40:GU60.
In GV40:GV60 I have the best positions per player.
In GW40:GW60 the second best positions, … in HQ40:HQ60 the 22nd best position.
In HR40:HR60 I have the number of games played per player.
In HS40:HS60 the total of games played.
In HT40:HT60 the percentage of games played per player.
I wish that it is clear what I want and that you can help me.
Thank you very much in advance!
Gr. Anthoney.
Hi ANTHONEY,
Thanks for your comment. You requirement needs the Excel file. Please share your Excel file and repost your problem on our official ExcelDemy Forum. Our experts will try to reach you out.
Regards
Rafiul Hasan
Team ExcelDemy