CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formulaarray-formulasrank
Law
Law

Reputation: 349

Sorting/Ranking Tied Scores based on Points Difference

I wanted to use Google Sheets to do a competition ranking which can help me to rank or sort the ranking automatically when I key in the Points. However, there is a condition where there will be a tied happens. If a tie happens, I will take the Score Differences (SD) into consideration. If the Score Differences is low, then it will be rank higher in the tie condition. See below table for illustration:

enter image description here

For example: Currently Team A and Team D having the highest PTS, so both of them are currently Rank 1. However, Team D is having a lower SD compare to Team A. So I wanted to have it automatically rank Team D as Rank 1 and Team A as Rank 2.

Is this possible?

Upvotes: 0

Views: 294

Answers (3)

player0
player0

Reputation: 1

=ARRAYFORMULA(IF(LEN(A2:A), RANK(H2:H*9^9-G2:G, H2:H*9^9-G2:G), ))

0

Upvotes: 0

Benoît Wéry
Benoît Wéry

Reputation: 862

With sort() you can define multiple sorting criteria (see [documentation][1], e.g. 

=sort(A2:I5,8,false,7,false)

So you're going to sort your table (in A2:I5, change accordingly) based first on PTS, descending, then on SD, descending? You can add more criteria with more pairs of parameters (column index, then descending or ascending as a boolean).

Then you need to compare your team name with with the sorted table and find its rank in the sorted list: 

=ArrayFormula(match(A2:I5,sort(A2:I5,8,false,7,false),0))

Paste that formula in I2 (assuming your table starts in A1 with its headers, otherwise adjust accordingly).

Upvotes: 0

Kei
Kei

Reputation: 1026

One solution might be to create a hidden column with a formula like:

=PTS * 10000 - SD

(Replacing PTS and SD with the actual cell references)

Multiplying PTS by 10000 ensures it has a higher priority than SD. We want to reward low SDs, so we subtract instead of add.

Finally, in the rank column, we can use a formula like:

=RANK(HiddenScoreCell, HiddenScoreColumnRange, 0)

So, for example, if the HiddenScore column is column K, the actual formula for row 2 might look like

=RANK(K2, K:K, 0)

The third parameter is 0 as we want higher scores to have a lower rank.

To sort, you can just apply a sort on the Rank column.

Upvotes: 1

Related Questions