Leggi questo articolo in Italiano
At work I use excel daily and I strongly believe that challenge yourself in apparently useless exercises as a Soccer ranking table, act as a springboard for new solutions and techniques that may be useful elsewhere in the future.
In case of Serie A (1st Italian Soccer league), the complexity is how to replicate and automate with formulas the tie breaker known as "classifica avulsa" and how to do it without macros. To explain what the "classifica avulsa" is: it is a table (or multiple tables) inside the main table used to determine only head-to-head results between teams with the same total points.
Here below is the complete file you can download. Further below the main explanations.
Rules of the championship
The first step to correctly implement the logic behind is to fully understand the logic itself.
As most of the European soccer leagues, the main ranking criteria is points (3 for a win, 1 for a draw and 0 for a loss): who has more points is in the lead. But what happens when two or more teams share the same points?
Let's make a first important distinction:
- Should the championship be not over yet, the following rules apply:
- Games played = teams with same points but less games played lead in the table
- Goal difference = teams with best goal difference (for vs. against) lead in the table
- Goals scored = teams with more goals scored overall lead in the table
- Draw = I applied the alphabetical order in this file
- Only when the championship is over, we can use the "classifica avulsa" method to rank teams sharing same points. Rules are as follows:
Games played = teams with same points but less games played lead in the table
- Points earned in head-to-head matches among all teams of the "classifica avulsa"
- Goal difference in head-to-head matches among all teams of the "classifica avulsa"
- Goal difference in the entire championship
- Most goals scored in the entire championship
- Draw = I applied the alphabetical order in this file
In this file, the championship shows that 3 couples of teams need the "classifica avulsa" method to reach the final ranking table.
This means that before compiling the final ranking table, we will have to build 3 separate ranking tables (one for each couple) to sort the order of these 6 teams.
But how to calculate it automatically and dynamically?
File contains 3 sheets: calendar, Ranking table and calc.
Sheets names are irrelevant though, because all file is based on Tables which makes it a little bit more dynamic.
In calendar sheet we have the list of all our matches from game 1 to 19. It's important to remember:
- Every match day has its own table (game1, game2, [...], game19) with the same structure for all. Header rows are hidden but are named as follows:
- res1: goals scored by team on the left (home matches)
- res2: goals scored by team on the right (home matches)
- home: team on the left
- away: team on the right
- res3: goals scored by team on the left (away matches)
- res4: goals scored by team on the right (away matches)
In Ranking table sheet we have the final ranking table after all calculations.
In calc sheet we have several data and calculations:
- Table helper: is a simple table which define the parameters of each match day table (name, how many rows it has and where is the starting row). This will allow us to list dynamically on the next table all matches one after the other.
- Table results: dynamically list all matches, calculate points earned by each team (home and away matches), goals for and against and provide a unique field for head-to-head lookup.
- Tables points and goaldifference: list points and goal difference between the team left side vs. its match on the top side. These tables will only be used in case the classifica avulsa method is required.
- Table ranking: it is the summary of all calculations: it lists and ranks teams by points, determines whether championship is over or not and so which ranking method to use (see above). It lists also team with same points which require the separate "classifica avulsa" ranking (calculated in the following table).
- Table avulsa: it determines for a maximum of 5 same-points circumstances (between 2 or more teams) the order among themselves. Of course the table can be expanded to handle more than 5, but this should be enough for almost all cases
What to change to adapt it to your needs
In case you would like to use the same structure for a different project (let's say the current year Serie A or your championship with friends that follows the same rules), here are the things you should be changing:
- In helper Table, in column TableName, you have to insert / substitute / add / delete names you have assigned to your matches tables, depending on the number of games and teams you have.
- In Tables points e goaldifference, as they are tables, headers cannot be defined dynamically. Therefore you will have to copy / paste the unique list of teams there at the top (teams list on the left side will populate accordingly).
- DONE. Didn't I promise an automated solution?
Last thoughts and notes
- I believe there are many options and methods you can use to tame the beast that is this complex tie breaker method. I could not find myself a solution that either required macros or free to use. Please feel free to reach me for comments and hints on how to make it better.
- File comes free of charge and without protections. I encourage you to use it, test it and I hope it will be useful to some of you. I don't care being mentioned or cited as the developer of this file should you like to share it elsewhere.
- There are many websites out there which help and explain how to use and combine different formulas to achieve the desired result. Few of them which helped me: get-digital-help.com, exceljet.net, blog.jamesbayley.com