How to Rank without Ties in Google Sheets

In the world of spreadsheets, knowing how to effectively rank data can be crucial. However, standard rank functions in Google Sheets, such as RANK.EQ and RANK.AVG, do not have built-in tie-breaking capabilities. This means that if multiple values share the same rank, these functions will treat them as equals. But fear not! There is a way to achieve rank without ties using Google Sheets, and I’m here to spill the beans!

Rank without Ties (Non-array Formula)

Let’s start with a simple non-array formula to assign ranks without ties in Google Sheets. Suppose we have a list of scores, and we want to rank them in descending order. The highest value will receive rank 1, and so on. Here’s how you can do it:

=RANK.EQ(C2,$C$2:$C$9,0)

Here, C2 represents the value you want to rank, and $C$2:$C$9 is the range of data. By dragging this formula down, you can assign ranks to all the values.

To break ties and ensure unique ranks, we can modify the formula as follows:

=RANK.EQ(C2,$C$2:$C$9,0)+COUNTIF($C$2:C2,C2)-1

This formula incorporates the use of COUNTIF, which returns the number of occurrences of each value. By adding this count to the original rank, we can effectively break ties and assign distinct ranks.

Top to Bottom (Desc. Order)

Let’s dive deeper into the top to bottom (descending order) approach. Suppose we have a list of players with their scores, and we need to rank them accordingly. If there’s a tie, we want to assign the same rank to the tied players. Here’s an example:

To achieve this, we can use the following formulas:

• D2:

  =RANK.EQ(C2,$C$2:$C$9,0)

• E2:

  =RANK.EQ(C2,$C$2:$C$9,0)+COUNTIF($C$2:C2,C2)-1

By copying these formulas down, you can assign ranks without ties to all the players.

Bottom to Top (Asc. Order)

Next, let’s explore the bottom to top (ascending order) approach. This is particularly useful in sporting events or competitions where the lowest value (e.g., finishing time) should receive the highest rank. Here’s an example:

To achieve this, you can use the following formulas:

• D2:

  =RANK(C2,$C$2:$C$9,1)

• E2:

  =RANK.EQ(C2,$C$2:$C$9,1)+COUNTIF($C$2:C2,C2)-1

These formulas work similarly to the ones used for descending order, but we specify 1 in the is_ascending parameter of the RANK.EQ function.

Array Formula to Rank without Ties in Google Sheets

Now, let’s take it up a notch with an array formula. In this case, we can utilize the COUNTIFS function to achieve the same result without dragging down the formula. Here’s how it works:

=ArrayFormula(IFNA(RANK.EQ(C2:C9,C2:C9,0)+COUNTIFS(C2:C9,C2:C9,ROW(C2:C9),"<="&ROW(C2:C9))-1))

This array formula expands the results in the range E3:E9, providing rank values without ties. If the range is not blank, it will return a #REF error.

For the bottom to top approach, you can use the following array formula:

=ArrayFormula(IFNA(RANK.EQ(C2:C9,C2:C9,1)+COUNTIFS(C2:C9,C2:C9,ROW(C2:C9),"<="&ROW(C2:C9))-1))

Formula Explanation

In the non-array formula, we used RANK.EQ and COUNTIF separately. In the array formula, however, we’re combining them to create a more efficient solution. Let’s break it down:

• Part 1:

  RANK.EQ(C2:C9,C2:C9,0)

  or

  RANK.EQ(C2:C9,C2:C9,1)

  This part returns the rank of all the scores. In the array formula, it expands to cover the entire range of data.

• Part 2:

  COUNTIFS(C2:C9,C2:C9,ROW(C2:C9),"<="&ROW(C2:C9))-1

  This part counts the occurrences of each value and adds them to the ranks. It expands similarly to Part 1, ensuring that the counts match the ranks.

By combining Parts 1 and 2, we can effectively assign ranks without ties using an array formula.

And there you have it! You’re now equipped with the knowledge to rank data without ties in Google Sheets. So go ahead, give it a try and enjoy the power of efficient data manipulation. If you want to learn more about Google Sheets and explore additional tips and tricks, check out Crawlan.com for more exciting content.

Thanks for joining me on this spreadsheet adventure. Happy ranking!