Ranks and Tiebreakers

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!

4 Likes

Dear @Joseph_B,

Thanks sharing your knowledge work and explaining it in a detailed way. :handshake:
I am sure it will come handy one day :diamond_shape_with_a_dot_inside:

1 Like