I needed to automate ranking teams with tie-breakers, and wanted to share my “oh duh” solution.
In the example below, teams are ranked based on the number of wins they have (more wins is better). If there is a tie in the number of wins, then the tie-breaker is points scored over the season (i.e. PointsFor, more is better).
To break down what is happening:
-
The Team column is simply the name of each team.
-
The Wins column is how many wins each team has.
-
The PointsFor column is how many points the team has scored over the season.
-
The RankWins column is the ranking of each team by how many wins they have. The formula used is:
Rank(thisRow.Wins,Tiebreaker.Wins)
While this formula ranks teams by wins, any ties are grouped together (e.g. the two teams with 5 wins are each ranked 2). Hence the reason for writing this post.
- The RankPointsFor column ranks the teams by how many points they scored compared only against other teams that have the same number of wins. The formula used is:
rank(thisRow.PointsFor,Tiebreaker.Filter(thisRow.Wins=Wins).PointsFor)
For example, the team with 7 wins is the only team with 7 wins, so that team would have a rank of 1 in the RankPointsFor column. But, the three teams with 1 win are ranked against each other based on the values in the PointsFor column.
- The RankFinal column is the end result we are seeking, and uses this formula:
thisRow.RankWins+thisRow.RankPointsFor-1
This formula combines the ranks from the RankWins and RankPointsFor columns and subtracts 1 to equal a teams final rank with the tiebreaker (PointsFor) factored in.
This can be extended to handle additional tie-breakers. It can also be done with less columns, but I prefer this method as it is easier to troubleshoot. I also sorted the table based on the values in RankFinal.
I’m sure there are other approaches, but hopefully this helps someone!
