Possible Rank Function and Sorting by 2 columns in a Scoring Ladder

[cRx45]

Hi All.

With the help of BrianMH I was able to get an issue sorted with my scoring spreadsheet in a previous thread.

Another issue has been brought to my attention now which I can't get my head around.

The file with dummy data can be found here. (Sheets are locked but have no password)

The issue is with sheet '3. Ladders'. If you look at the equal 1st and equal 5th teams, you'll notice that the 2 teams that had more 'wins' are underneath the teams with 'less' wins. So even though the first 2 are equal first on points, they need to be sorted by wins also (this would them make them 1st and 2nd, not equal 1st).

The same issue can bee seen with the players ladder where one of the equal 2nd players has 3 losses but is above a player that only has 1 loss (again resulting in that player now being 4th and the other 2 equal 2nd).

I'm using the Rank function (on sheet 2., columns A and O) to rank them by the points column (C) and wins column (O) respectively.

Does anyone have any ideas how to solve this without messing up any of BrianMH's fixes? Or do I just need to add more criteria to the Rank functions on sheet 2.???

Thanks

 

[barry houdini]

....Or do I just need to add more criteria to the Rank functions on sheet 2.?....

Yes, that's what I'd do....

Try this formula in sheet 2 cell A2 copied down

=IFERROR((RANK(C2,$C$2:$C$17)+COUNTIFS($C$2:$C$17,C2,$D$2:$D$17,">"&D2)+COUNTIFS($C$2:$C2,$C2,$D$2:$D2,$D2)-1),"")

and you can do a similar thing in column O except you would use < in place of > because you are dealing with losses rather than wins

 

[cRx45]

=IFERROR((RANK(C2,$C$2:$C$17)+COUNTIFS($C$2:$C$17,C2,$D$2:$D$17,">"&D2)+COUNTIFS($C$2:$C2,$C2,$D$2:$D2,$D2)-1),"")

Thanks Barry! You are a legend.

That worked a charm.

Thanks for your help.