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:

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 structure

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:

In ranking_table sheet we have the final ranking table after all calculations.

In calc sheet we have several data and calculations:

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:

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