How to handle a season of Serie A soccer league with excel without any macro
In this article I am going to explain how to handle with excel a ranking table which requires a tie breaker setup.
As an example, I took the Serie A (1st Italian Soccer league), where the complexity is how to replicate and automate with formulas the tie breaker in use known as classifica avulsa. Let's clarify what it is:
Classifica avulsa is a criteria used to determine the final ranking order for all those circumstances where at least 2 teams ended up with the same amount of points.
It means that for each group of teams sharing same points, we will have to build a separate (and always updating) ranking table that will determine the final position for each team.
Download the example file (no macro):
Serie A 2015-2016
Rules of the championship
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.
When 2 or more teams share the same points, here are the rules:
- Should the championship be not over yet:
- Games played = teams with same points but less games played lead in the table
- same parameters used for final classifica avulsa (see below)
- When the championship is over:
- Classifica avulsa rules:
- 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
- Classifica avulsa rules:
At the end of Serie A 2015/2016, there were 3 couples of teams which needed the classifica avulsa criteria 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.
File contains 3 sheets:
Sheets names are irrelevant though, because all file is based on Tables which makes it a little bit more dynamic.
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)
ranking_table sheet we have the final ranking table after all calculations.
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 are used in the classifica avulsa calculation.
- Table ranking: it is the summary of all calculations: it lists and ranks teams by points, determines how many teams share same points and define separate ranking tables to be calculated with the classifica avulsa method.
- Table avulsa: it determines for a maximum of 8 same-points circumstances (between 2 or more teams) the order among themselves.
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).
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, so here it is my solution.
The file is shared just to show how I achieved handling this tie breaker with excel. Any further usage of the file is your responsibility.
I encourage you to write me in case you'd find some errors.
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