How to Rank without Ties in Google Sheets

If two people have the same score or equal marks, their rank will be the same. If you want to break the ties or rank without ties in Google Sheets, you can use a combination formula.


There are two rank functions in Google Sheets to assign ranks to numbers in a list – RANK.EQ (RANK) and RANK.AVG. At present, none of them have tie-breaking capability.


If there is a tie in ranking because of the same score, marks, etc., the first function will return the same rank, whereas the second function will return the average rank.


That doesn’t mean rank without ties is not possible in Google Sheets. We can use RANK.EQ with COUNTIF for the same.


Must Read: How to Use All Google Sheets Count Functions [All 8 Count Functions].


The above combination will also work with an array formula to return rank without ties in a list/array.


First, I am starting with the non-array formula so that you can understand the logic.


Rank without Ties (Non-Array Formula)


Here is an example.


rank without ties non-array formula in Google Sheets
image – 1

In the above example, the players “Karen” and “Kristi” have the same score, i.e., 5.


So the rank of those persons is 4 (please refer to column D). Cell D2 has the following formula, which I have copied down until cell D9.


=rank(C2,$C$2:$C$9)

In the formula, the cell reference C2 is the value, and the cell range reference $C$2:$C$9 is the data as per the syntax of the function RANK.


RANK(value, data, [is_ascending])

The value must be in relative reference mode, whereas the data must be in absolute reference mode. Because when we drag down the formula, we want to take new values from every row.


You May Like: Placement and Use of Single or Double Dollar Symbols in Formula in Spreadsheet.


To assign rank without ties in Google Sheets, I have used the below RANK.EQ + COUNTIF combination in cell E2, which copied down.


=rank.eq(C2,$C$2:$C$9)+COUNTIF($C$2:C2,C2)-1

Let’s see what the above COUNTIF does.


count of occurrence -1 in ranking
image – 2

The COUNTIF above returns 0 for the first occurrence of a score and 1 for the second occurrence. We add this occurrence to the RANK to break the ties.


Array Formula to Rank without Ties in Google Sheets


In the Array Formula, we won’t use COUNTIF. Then?


We will use COUNTIFS. But the logic will be similar to the above non-array formula, that is, adding occurrence with rank.


Insert the below formula in cell E2 (please refer to image – 1 above).


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

It will expand the results in the range E3:E9. If the said range is not blank, then it will return #REF! error.


In the non-array formula, we have used RANK.EQ as well as COUNTIF in non-array form. Here, on the contrary, we are using array formulas.


You can read the rank without ties array formula details under the formula explanation part below.


Formula Explanation


There are two parts in the formula.


Part # 1:


rank.eq(C2:C9,C2:C9)

Part # 2:


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

Both the above parts use the ArrayFormula function to expand.


The first one returns the rank of all the scores (please refer to D2:D9 in image – 2), whereas the second function returns the count of occurrences (please refer to E2:E9 in image – 2).


That means Part # 1 is the array formula use of the RANK.EQ. But Part # 2 may be new to you. I have included Part # 2 details in a separate post here – Running Count in Google Sheets.


As a side note, I have another array solution for the tie-breaking in ranking. Though the RANK.EQ + COUNTIFS is the simplest one I am sharing that too here – Flexible Array Formula to Rank Without Duplicates in Google Sheets.


That’s all about rank without ties in Google Sheets.


Thanks for the stay. Enjoy!

Disqus Comments

Hot Posts