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.

**Rank with Ties in Excel : 5 Suitable Methods**

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)-1`

**Formula 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

- The value returned by the

- 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

- The value returned by the

- 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 4
^{th}). - The rank of the preceding participant named Brad remains 4
^{th}while the following participant Chris is now ranked 5^{th}.

**Read More:** Rank IF Formula in Excel

**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 4
^{th}). - The rank of the first participant named Brad remains in 4
^{th}position while the following participant Chris is now ranked 5^{th}and the last participant Tobey holds the 6^{th}position.

**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 4^{th}). - 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. 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 4^{th}, 5^{th,} and 6^{th} Â positions among the 3 participants holding the same 4^{th} positions. As we have 3 participants holding the 4^{th} rank or position, another 2 participants are not available to hold the 5^{th} or 6^{th} 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 9
^{th}in this list has got $20. Note that, the prize specified for the 9^{th}position 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 4
^{th}position has got the average of the 3 prizes specified for the 4^{th}, 5^{th}, and 6^{th}.

**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 5^{th} rank with 3 participants belonging to this rank. But the **RANK** formula has also skipped the 6^{th} and 7^{th} positions. The 2 participants supposed to hold these ranks now belong to the 5^{th} position. So, we want to have these 2 ranks occupied but without breaking the tie in the 5^{th} 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))`

**Formula Breakdown:**

**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**.

**Note:**We have sorted our worksheet based on the existing General Rank before applying the above formula.Â Please see this tutorial on How to Sort and Filter Data in Excel.

**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 5
^{th}rank has not been broken. But Excel has not skipped the 6^{th}and 7^{th}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.

**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.

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**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!!!

**Related Articles**

- Excel Formula to Rank with Duplicates
- How to Rank in Excel Highest to Lowest
- How to Calculate Top 10 Percent in Excel
- How to Rank Within Group in Excel
- Ranking Data in Excel with Sorting

**<< Go Back toÂ Excel RANK Function | Excel Functions | Learn Excel**

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 HasanTeam

ExcelDemyHello

I am trying to rank students scores in 3 groups, for example (A2:A21,A22:A43,A44:62).I don’t use table and I also would like the Range works dynamic, that’s when the ranges exchange from A2:A21 to A2:A25 and A22:A43 to A26:A50 and so on, the formula works correctly.

If two students have the same score they will receive the same rank. For example 20=1,20=1 , 19.25=2 and 19=3

Hello

JOHN,Thanks for your comment. If new value is inserted in the range, the range changes (for example, from

A2:A21toA2:A25). You want to make the formula dynamic for the changed range. It seems you don’t want to use table. Actually, without using table, you won’t be able to make the formula dynamic.The

RANK functionand theRANK.EQ functionallow you to make the rank dynamic. But these functions assign the same rank to tied numbers and does not leave gaps in the ranking sequence. For example, if there is a tie for the second and third positions, both positions will be assigned a rank of 2, and the next distinct number will be ranked 4. But if you want to fill the next rank after ties, these functions are not useful. In that case, you have to use any of the methods stated in the article and copy the formula for the newly inserted value in the range. Without using table, it is not possible to make the formula dynamic.Regards

Rafiul HasanTeam

ExcelDemy