[SOLVED] Ranking with multiple tie breakers doesn't work properly

[Anthoney]

Hi
I’m trying to create a score sheet.
When there are duplicate scores, I want to check:

• Have the players played the same amount of games?
• If not, the player with the most games played has the advantage.
• If yes, on which positions have the players finished the game, and which player has the highest positions.

The formula I’ve used to check this is:

=RANK.EQ($F83,$F$83:$F$96)+COUNTIFS($F$83:$F$96,$F83,$B$43:$B$56,">"&$B43)+COUNTIFS($F$83:$F$96,$F83,$G$83:$G$96,"<"&$G83)+COUNTIFS($F$83:$F$96,$F83,$H$83:$H$96,"<"&$H83)+COUNTIFS($F$83:$F$96,$F83,$I$83:$I$96,"<"&$I83)+COUNTIFS($F$83:$F$96,$F83,$J$83:$J$96,"<"&$J83)+COUNTIFS($F$83:$F$96,$F83,$K$83:$K$96,"<"&$K83)+COUNTIFS($F$83:$F$96,$F83,$L$83:$L$96,"<"&$L83)+COUNTIFS($F$83:$F$96,$F83,$M$83:$M$96,"<"&$M83)

As U can see in the screenshot for the most part the calculations work, except for players Kappa & Lambda, who are tied on 38 points.
Player Lambda should have been ranked position “8” and player Kappa position “9”.
This, because the highest finish for player Lambda was “4” and for player Kappa it was “6” (column G).
But they’ve both been ranked “11”, which doesn’t make any sense IMO.

I did some testing and the problems start after I add the tie breaker for column J.
But the strange thing is that when U check players Alpha and Eta, who are tied on 120 points, the formula works perfectly, even though the positions of the games are the same till column M.

Instead of RANK.EQ I’ve also tried COUNTIF, but then it also goes wrong.
And I’ve tried with SUMPRODUCT instead of COUNTIFS, but then it even goes more wrong.
So can someone please help me out?

Since I’m no spreadsheet expert, please don’t answer in general terms.
When U’re so kind to answer, please be so friendly and adjust the formula and/or the sheet.
Thank U very much in advance

ScoreSheetv1.0.ods (69.2 KB)

Gr. Anthoney

[schiavinatto]

Changed cells are marked with a red frame.

ScoreSheetv1.0_GS.ods (69.1 KB)

For the classification, I considered the LOWEST position and the total SUM of points.

[Anthoney]

Thank U so much for U swift and elegant solution @schiavinatto!
Like @karolus U also didn’t take into account the number of games played.
But by adding the count of the number of games in column AE and changing Ur formula in column AF from

=1-((AD23/1000)+AC23)/100

to

=1-((AE23/AD23)/1000+AC23)/100

I was able to solve this issue.
Player Nu is now ranked “12” and player Xi ranked “13”, since Nu played 6 games and Xi only 1.

I do have a small issue though…
The sheet is only a practice sheet.
The final sheet will have 20 players per game and 25 games in total.
Therefore I have changed Ur formula in column AD from

=SUM(G23,J23,M23,P23,S23,V23,Y23)

to

=SUMIF($G$22:$AA$22,"POS",$G23:$AA23)

Something similar I would like for the formulas in column AC

=SMALL(G23~J23~M23~P23~S23~V23~Y23,1)

It’s not a big deal, but if U would know a better formula that would be great.

Thanks again!

ScoreSheetv2.0.ods (55.2 KB)

[Anthoney]

In case anyone is interested, I’ve managed to get rid of the zeros which were returned in the sorted list by adding &"" to the formulas.
So in G3 it was

=INDEX(G$23:G$36,MATCH(1,($AH$23:$AH$36=$AH3),0))

And I’ve changed it to

=INDEX(G$23:G$36,MATCH(1,($AH$23:$AH$36=$AH3),0))&""

ScoreSheetv3.0.ods (53.4 KB)

[karolus]

Formula for F23: ( pull down to F36 )

=SUMIF($G$22:$AA$22;"PTS";$G23:$AA23)+ROW()/10000

Formula for F3: ( pull down to F16 )

=LARGE(F$23:F$36;D3)

Formula for E3: ( pull down to E16 )

=INDEX(E$23:E$36;MATCH(F3;F$23:F$36;0))

Formula for G3: ( pull down to G16 followed by pull right to AA16 )

=INDEX(G$23:G$36;MATCH($F3;$F$23:$F$36;0))

Format F3:F16 and F23:F36 to show only whole numbers
94445_ask_simplified.ods (50.5 KB)

[Anthoney]

Thank U very much for Ur effort @karolus!
The problem with Ur solution is that U didn’t take into account the amount of games played.

@Anthoney

If not, the player with the most games played has the advantage.

The idea behind is that it’s better to have played a game than to not have played.
But thanks anyway!

[karolus]

@Anthoney

The idea behind is that it’s better to have played a game than to not have played.

How high should the number of games played be scored relative to the total points scored?

=played_games / ?

[Anthoney]

I was just finalizing my reply to Gilberto when Ur question came in.
As U can see there I have managed to solve the issue with players Nu & Xi.
But to answer Ur question, there is not direct relation to the number of games played and the total points scored.
In case of a tie it just depends on who has played the most games.
If that is also tied, like with players Alpha & Eta and Kappa & Lambda, then the positions come into play.
In v2.0 I have managed to do that with help of Gilberto’s solution.

[robleyd]

Please can you avoid textspeak? U → You, Ur → your or you are etc.

[Anthoney]

I write that way because I believe that You are just as important as I am.
So why write I with a capital and not write U?
But I understand that it can be confusing, especially with spreadsheets.
So I won’t do it anymore here