English Premier League

I play with numbers, I play with Excel, I play with R. I do original work and I do ordinary every ay work. The purpose of this insight is to share with you the results of my adjusted view of the English Premier Leagure after 19 games in the 2017/2018 season. The Features of my Model section helps to illustrate that this Excel file is relatively advanced in terms of the functions I have used in my work!

My concern is that whilst Manchester City are doing extremely well this season, their squad has cost a massive fortune to assemble, the squad of many EPL clubs comprises many foreign rather then home grown talent and some clubs have a very poor disciplinary record.

I decided to set up my analysis to show who would be top of the table if we adjusted the number of points awarded for the value of a club’s squad, for the nube of foreigners who are in a club’s squad and for their position in the fair play league.. Three variables, all equally weighted. What you see in the table below is the official league table on the left and my adjusted point values on the right. I have highlighted the new top 5 clubs.

EPL_Adjusted_Poisitions_a

Burnley currently as 32 points in that table which would be adjusted upwards to 105.8 whereas, you can see that whilst Manchester City has 55 points at the moment, those points would shrink to just 19.4 under my adjustment scheme.

Features of my Model

Look at

  • how I have made the adjustments, using MEDIAN values, for example.
  • how I have used the VLOOKUP function
  • this formula in cell H5 of the summary tab: =OFFSET(‘working (2)’!R$7,MATCH(B5,’working (2)’!$R$8:$R$27,0),-4)
  • the REPT function together with some Conditional Formatting for the graphic in column D of the summary tab
  • how I have used the MMULT() and MINVERSE() functions to prove the points awarded in the fair play table on the fair tab
  • how I have used the SUMPRODUCT() on the fair tab, too

You can download the file from the link that follows but remember two things: it is a draft file and not perfectly created; and this is just for fun and information and I don’t necessarily think that we should adopt my adjustment scheme! What you see above is taken from the working (2) tab of the file.

Download EPL_Adjusted_Poisitions

Duncan Williamson

27th December 2017