Leggi questo articolo in Italiano
After explaining how to handle a soccer Serie A league championship type (go to that article), here is another example on how to handle with excel a UEFA Champions League Group stage.
Again the complexity is always given by the tie breaker variable and unlike a normal Championship, with 8 Groups we have to build 8 different and separate standings.
Let's first share the file (without any macro) and refresh a couple of concepts.
Rules of the Tournament
As always, the starting point is to read the rules and understand the logic. At this link you can download the official UEFA regulations document valid for 2015-2018.
In case the link would not work anymore, here is the document again.
What is relevant for our exercise is Article 16 (Match system - group stage) and Article 17 (equality of points - group stage).
- Although a match sequence applies (i.e. Matchday 1: 2v3 and 4v1, etc. depending on the group stage draw teams order), due to teams of the same city not to play home matches on the same day or other criteria, this logic could not be implemented in the file.
- It is not stated what criteria applies if all group matches have not been played, therefore I used the common one with points > goal difference
- If two or more teams are equal on points on completion of the group matches, the following criteria are applied in the order given to determine their rankings:
- higher number of points obtained in the group matches played among the teams in question;
- superior goal difference from the group matches played among the teams in question;
- higher number of goals scored in the group matches played among the teams in question;
- higher number of goals scored away from home in the group matches played among the teams in question;
- if, after having applied criteria 1 to 4 teams still have an equal ranking, criteria 1 to 4 are reapplied exclusively to the matches between the teams in question to determine their final rankings. If this procedure does not lead to a decision, criteria 6 to 12 apply;
- (not in the file) superior goal difference in all group matches;
- (not in the file) higher number of goals scored in all group matches;
- (not in the file) higher number of away goals scored in all group matches;
- (not in the file) higher number of wins in all group matches;
- (not in the file) higher number of away wins in all group matches;
- (not in the file) lower disciplinary points total based only on yellow and red cards received in all group matches (red card = 3 points, yellow card = 1 point, expulsion for two yellow cards in one match = 3 points);
- (not in the file) higher club coefficient (see Annex D).
To replicate this in our excel, we have to build 8 different tables that can foresee 4 different cases:
- 1 standing for:
- 4 teams with same points (almost impossible, but still possible if all matches end with a draw)
- 3 teams with same points (I believe all with 12 points and the last with 0)
- 1 couple with same points (this has higher probability)
- 2 standings if 2 couples have same points (say 2 teams with 9 and 2 teams with 3)
What to change to adapt it to your needs
I will not go through the structure of the file here and this logic applies really for the UEFA Champions League only.
Still, if you would like to use it for a "personal" Champions League tournament, here are the things you should be changing (rest is automated):
- Team names and matches
- Should you have less groups (<8), you can just remove the tables and rows in Standing and remove and resize the tables in calc for the unused groups.
- Should you have more groups (>8), this is a bit harder as you are not the file maker, but just write to me and I can help you with that.
- As said this file is specific for the UEFA Champions League, therefore some adjustments are needed if you have more than 4 teams per group. Again please write to me and I can help you with that.
As always, 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.