Last year, I started writing an article on using Excel for a football league to work out the positions in the league table based on the results. I never finished it but then during the current pandemic and the uncertainty over completing the current football seasons I started playing with Excel again. So, this is not really a technology article, although in the Excel examples, I have used some useful Excel functions. This is also not an important article considering the global crisis surrounding the COVID-19 pandemic.
Those of us who are football (soccer) supporters, or supporters of any sport, will of course be wondering when and if the football season will continue. I am in England and I know it affects other countries leagues too but I will use the English top flight division in this article. It was going to be called, English Football quandary and using Excel to calculate league position but that’s a bit long.
So, if you do not already know what is happening, most teams have played 29 out of their 38-game season. Liverpool are leading by 25 points and will likely win it. Mathematically, Manchester City can still will the league, so the title cannot be awarded.
However, it is not all about the winner, as positions 1 to 4 will occupy the following seasons Champions League positions. Assuming the two domestic cups are won by teams in positions 1 to 7 then positions 5-7 will have entry to the Europa Cup competition next season. The bottom 3 positions get relegated. None of those positions are confirmed and even a team currently in 10th place can be relegated or a team in the bottom 3 could still qualify for Europe competition. At this time, mathematically, anything is possible; although only Liverpool or Manchester City can win the league.
The most important thing at this time is the health and safely of the nation (and all nations). Football will be far from the minds of most people right now. It’s not even been a week and two more weeks of staying indoors seems a long time right now. So, this article is a bit of light entertainment for everyone who is staying home, staying safe, protecting the NHS and saving lives but misses football.
There are many options being considered for what will happen to this season and so I will run through a few of these.
Finishing the 2019/20 Season after a delay
A short delay to the remaining fixtures until it is safe to play again is the best outcome. A long delay would more than likely involve compacting or cancelling the 2020/21 season. Starting the 2020/21 season more than 6 weeks late is likely to cause problems.
Even if Internationals were cancelled throughout the season and the much-needed February break was scrapped, the top clubs fighting on many fronts find it hard to fulfil their fixture list. Domestic or European competitions could be scrapped for 2020/21 but a lot of clubs rely on that revenue, not just in the Premier League but in all of the divisions. A good cup run can keep a lower league club running for a few years.
Squeezing in a partial 2020/21 season or cancelling next season to finish this one is not an attractive idea. TV companies that pay vast sums to show live games would not pay and would likely sue the Premier League for breach of contract. Most clubs have already spent the TV money and would not survive without it.
We also have not considered player contracts. Many clubs who have players that are out of contract on June 30, 2020 and maybe starting with a new club in July. If the season overruns these players will be in limbo. They will not be contracted to play for their current club. The current club cannot add to their squads when the transfer window opens because players are registered for the season. I know a rule change could be put in place to make an exception under these exceptional circumstances but as it stands now clubs can be significantly weakened for the remaining games if the season goes beyond June 30.
Fishing the 2019/20 season behind closed doors
If the public cannot go out, then players cannot play games behind closed doors either. They would be getting within 2 metres of an opponent that was not living in their household. Games behind closed doors involve more than just the players, grounds-staff, managers, coaches, TV people, officials to name but a few.
Unless they had all had been quarantined together in some way and all games commenced behind closed doors on a neutral ground, they would get exposed. They would still have to come into contact with people from the outside world when moving from accommodation to playing time and then could get and pass on the virus.
Let us say that the season restarted and a player from one team became infected. All his team-mates would soon follow because they train together and because symptoms are not immediate they could be playing matches and other players could get infected too. Then all players on all teams get the infection and one or two die because of it. The risk is not worth the result.
Ending the 2019/20 season early
Most teams have played 29 games so far in the 2019/20 season but not all. Ending the season with positions as they are now, would be unfair to those that have played a game less. Whilst it does not affect the 2nd spot for Manchester City, it does affect the positions of the other teams. Aston Villa are one win away from being above the relegation spots and both Arsenal or Sheffield United could secure European places.
The season could be ended after all teams had played each other once for the first time. I’ve heard that suggested as an option so have done the calculations here. The table would look like this after each team has played each other once for the first time compared to the table as it currently stands after 28/29 games.
Bournemouth and Spurs would be happy but Manchester United and Watford would not.
Abandoning the 2019/20 season
No-one wants to abandon the season after playing 29 games but as the clock ticks on, it may be the safest and simplest solution. The likelihood of things getting back to some semblance of normal looks a distant prospect and may already affect the start of the 2020/21 season without finishing the 2019/20 season.
It could also be the most cost effective especially if the TV companies do not penalise the leagues and clubs for abandoning the remaining games. It would certainly be cheaper for clubs to miss on average 9 or 10 games (including cup games) rather than a whole season.
Also, we do not know yet how far COVID-19 was really spread. I have seen it reported that it could have been in the UK from mid-January. As we know, symptoms do not show immediately and some people get mild symptoms when compared to others. Mild symptoms are not tested. Some players could have under-performed because they had the virus but only had mild symptoms. If that’s the case those matches should be replayed.
In my opinion, the current season should be abandoned if it is not 100% safe to play the games in front of large crowds. It is not just about the young, fit and healthy; the attendees or playing staff will all have families and could return home or visit relatives carrying the virus.
Of course, I would like to see another team that has not previously won the Premier League title achieve that but abandonment is the only safe option.
Previous abandoned season
The English Football Leagues were abandoned in 1939 and restarted again nationally in 1947. The 1939/40 season was abandoned when the UK went to war with Germany on September 3, 1939. Large gatherings of crowds were prohibited under the Emergency Powers (Defence) Act 1939. A lot of players joined the war effort and the teams that restarted the 1947/48 season as they did at the start of the 1939/40 campaign but without the 1939/40 fixtures played.
Blackpool would have won the 1939/40 First Division title if the positions had stood. If the games played in the 1939/40 season had carried over to the 1947/48 season then Stoke City would have won the league rather than Liverpool on Goal Ratio. Before the 1976/77 season, Goal Ratio (average of goals for divided by goals against) was used rather than Goal Difference.
Stoke performed worse than Liverpool on the corresponding fixtures during the 1946/47 season compared to those played in the 1939/40 abandoned season. So, if the 1939/40 results had been carried over to 1947, they would have won the league. Only two teams got relegated so that would not have made a difference to those positions.
VAR Lessons
This 2019/20 season has been filled by controversy and disillusionment. The Video Assistant Referee (VAR) has made some incorrect decisions or taken far too long to make judgements ruining the game for many supporters who are now wary of celebrating a goal.
Some of the VAR decisions are an interpretation by a different referee rather than fact. The law says level is onside but should the lines drawn for offside decision be taken when the passing payer touches the ball or when the ball leaves his foot? If they can’t make a decision within 30 seconds, it needs to either be reviewed by the referee on the pitch side monitor or go with the referees on field decision. The law makers should use what they have learned and change the laws of the game to fit in with VAR so we can all have a better season when it resumes.
Calculations to determine league position
The league is calculated firstly by how many points a team has and where those point totals are equal by a goal differentiator. As mentioned, this was Goal Ratio (or average) before the 1976/77 season and Goal Difference from then on. Should points and goal differentiator be equal there have been other factors used to determine position over the years.
For now, we’ll keep it simple. Leagues before 1976/77 will use Goal Ratio (GR) and from 1976/77 will use Goal Difference (GD). Regardless of which is in use, the methods here are quite similar. GR is always zero (scored no goals) or a positive number whereas GD can be positive or negative.
In these examples, you will also notice the points awarded for a win are different. In England, 2 points were awarded for a win before the 1981/82 season with 3 points awarded thereafter. 1 point is awarded for a draw and 0 points for a loss.
To work out the position we need a single calculated number to rank. We want to make sure the GR doesn’t affect the total points.
As it is always zero or a positive number, we can just add it to the points as long as the number is smaller than one point. To have an average we cannot use zero when dividing. We will use a minimum of 1 rather than zero for the total goals scored in a season (or partial season).
Therefore, a team scoring 40 goals but conceding 0 goals all season will have a GR of 40. A team conceding 40 goals but scoring zero in a season will have a GR of 1/40 (or 0.025). Both of these events are highly unlikely but it is mathematically possible to have conceded a total of 0 or to have scored a total of zero especially early on in the season. If every game is drawn 0-0 then 1/1 will equal a GR of 1.
With the possibility of getting a GR in excess of 100 (if the opponents don’t score against you all season) and without affecting the points total, we should divide it by 1,000 before adding it. Dividing by 1,000 will also apply to GD later on.
So, lets use an example of Team A with 45 total points achieved over a season with 50 goals scored and 25 conceded in that season.
Team A: 45 + ( ( 50 / 25 ) / 1000 ) = 45.0020
Team B achieved the same number of points but scored 40 goals and conceded 50 goals so their ranking calculation would be.
Team B: 45 + ( ( 40 / 50 ) / 1000 ) = 45.0008
The team in the first example will be placed above the team in the second example.
Goal Difference works the same way but because it can be negative, we have to remember to add 0.5 before applying the goal difference so as not to affect the points total.
Using the same examples, Team A will have a GD of +25 whilst Team B will have a GD of -10. Here are the example calculations:
Team A: 45 + 0.5 + ( ( 50 – 25 ) / 1000 ) = 45.5250
Team B: 45 + 0.5 + ( ( 40 – 50 ) / 1000 ) = 45.4900
If you wanted to also add Goals Scored in case points and goal difference are the same then take the goals scored and divide by a million or other high number before adding it to the equation. That is so goals scored doesn’t affect goal difference.
Using Excel to show league position
The INDEX function is used to look up values from an array of numbers both horizontally and vertically. You can also visit my article Finding a value in a range using Microsoft Excel functions that better describes the INDEX function.
The important function here is RANK.EQ (it used to be called RANK in previous versions of Microsoft Excel). For our purposes, RANK.EQ takes an array of numbers, orders them from highest to lowest and reports the position within the array. As we have used the formula above, we should obtain unique values for all teams but there will be occasion where both values are equal and in that case RANK.EQ will give them both the same position in the array and skip the duplicate position.
For example, values 10, 15, 19, 7 and 15 would rank 19 first, the two instances of 15 second, the 10 fourth and the 7 last in 5th place.
If you would like to see the calculations above then download the following Excel examples:
4 or 20 or 24 team league template
The Premier League after 19 games
Example of abandoned 1939-40 league points carried forward to 1946-47 season
Final notes
In my opinion, if we cannot get back to normal and fulfil the football league fixture list with fans in attendance before the end of June 2020, then the league has to be abandoned and reset back to zero points for 2020/21 to have a chance to start.
Blackpool lost out on winning the title after the 1939 abandonment previously so this is not something new. Liverpool also benefited with a league title because the 1939 results were not held over until the league could resume in 1947.
By the way, this article was just a bit of fun whilst cooped up indoors but I work from home and don’t have the time to respond to comments. Football is a passionate subject and rivalry amongst fans can get out of hand. This is a technology blog so not the place for football rivalry. I will look at any comments when I get some free time but they will not be published automatically. Only add a comment if it is constructive to the article and you think that I would consider it worthwhile publishing. If not, feel free to share and argue amongst yourselves on social media.