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:
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:
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:
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!