In Part V we are going to look at
refining our xGoals calculation building league tables and evaluating teams over/under performance. With games coming thick and fast this past week, I will wait for a lull in fixtures (after GW 32) to write about improving our xGoal calculations using Attack and Defence ratings.
In this part of our tutorial, we’ll be using our RANK function again to automate everything so it updates after every game week. We’ll also be learning about some metrics commonly used in fanalytics.
**Part V includes results from GW 31, so be sure to update your results or our numbers won’t match.
Building a League Table
To start, create a new worksheet in our workbook and name it League Table. In cells A100:A119, add each team’s name (or copy it from one of our earlier tables).
This league table will look at overall results, home results, and away results. so in row 99 add the following headings so our table looks as follows:
We’ll start with the home section. In cell N100, find how many games each team played at home using a COUNTIF function: =COUNTIF(Results!$C:$C,’League Table’!$B100)
In cells O100:Q100, find how many wins, draws, and losses Arsenal have at home:
=COUNTIFS(Results!$C:$C,’League Table’!$B100,Results!$G:$G,”H”) **Change H to D for draws, and to A for losses.
In R100, SUM how many goals Arsenal have scored at home:
SUMIF(Results!$C:$C,’League Table’!$B100,Results!E:E) notice here that I have not wrapped E:E in $ signs. This means that when we drag this across to cell S100, it will sum the goals conceded.
So far, our table should look as follows:
In T100, we’ll calculate TSR (Total Shots Ratio). TSR is a metric that calculates what % of shots a team is taking in its games. It ranges from 0% to 100%, 100% meaning that a team is taking all of the shots in games they play and facing none. The formula is:
Shots For/ (Shots For + Shots Against)
For our workbook then, in order to find a team’s Home TSR, we use the following:
=SUMIF(Results!$C:$C,’League Table’!$B100,Results!$L:$L)/(SUMIF(Results!$C:$C,’League Table’!$B100,Results!$L:$L)+SUMIF(Results!$C:$C,’League Table’!$B100,Results!$M:$M))
In U100, we’ll calculate SoTR (Shots on Target Ratio). This is the same concept as TSR, except for Shots on Target.
Shots on Target For/ (Shots on Target For + Shots on Target Against)
For our workbook, the formula is:
=SUMIF(Results!$C:$C,’League Table’!$B100,Results!$N:$N)/(SUMIF(Results!$C:$C,’League Table’!$B100,Results!$N:$N)+SUMIF(Results!$C:$C,’League Table’!$B100,Results!$O:$O))
In V100, we’ll look at GR (Goal Ratio). This looks at % of Goals Scored/Conceded.
Goals For/(Goals For + Goals Against)
Or for us:
=R100/(R100+S100) as we already have the goals scored and conceded in our table.
In W100, we’ll look at Accuracy. This looks at the % of shots a team takes that hit the target.
Shots on Target For/Shots For
=SUMIF(Results!$C:$C,’League Table’!$B100,Results!$N:$N)/SUMIF(Results!$C:$C,’League Table’!$B100,Results!$L:$L)
In X100, let’s see how well teams finish their shots on target:
Goals For/Shots on Target For
Lastly, in Y 100, we’ll look at Save%. This looks at how many shots on target are not goals conceded.
(Shots on Target Against – Goals Against) / Shots on Target Against
=(SUMIF(Results!$C:$C,’League Table’!$B100,Results!$O:$O)-S100)/SUMIF(Results!$C:$C,’League Table’!$B100,Results!$O:$O)
This leaves us with:
Do the same with the away results so it looks as follows:
Here is how I have done the tables:
You’ll see in the first column I have rankings. Let’s build those now.
In AZ101:BB101, add the following headings: Overall, Home, Away.
We’ll make another drop down list as we did in our Odds Calculation worksheet. In cell A2, go to your toolbar, select DATA>DATA VALIDATION select LIST and use the following reference: =$AZ$101:$BB$101
This gives us a drop down list with Overall, Home, and Away.
Back down at cell AZ102 use the following formula:
This calculates the total points for Arsenal and adds their Goal Difference divided by 10000 and their Goals For divided by 10000. This avoids issues with ranking if you have the same values.
Do the same for the home and away games so we get the following:
Note here that we are multiplying wins by 3 and adding draws, then adding a decimal version of Goal Difference and Goals Scored. So follow the same steps for the home section of our table and away section. We can use these numbers to rank our teams.
Using the RANK formula we have previously discussed, rank the Overall, Home, and Away values in columns BD:BF. The formula for the overall rank is:
Copy it down and across. You should have something that looks like this:
So now in cell A102 we can use the following formula to rank each team either overall, when at home, or when away:
Copy this down for all teams.
Now we need a consolidated table. So:
- In cell A129 use the formula =A102
- In cell B129 use the formula =B102
- In cell C129 use the formula =IF($A$2=$AZ$101,C102,IF($A$2=$BA$101,P102,IF($A$2=$BB$101,AC102)))
- copy this down and across to column N. This leaves us with the following:
So now we have a single table for whether we want to look at Overall results, Home results, or Away results. Let’s get these in order.
In cells A5:A24 add the numbers 1 to 20. In B5 use the following formula:
Copy this down and across and . . . that’s it. Last step is to add headings to the columns and merge cells A2:P3 so you have this:
And here’s a gif:
There are a variety of ways to calculate expected points. I’m going to outline two.
Method #1 is very straightforward.
Firstly, find the average points so far this season. As of Gameweek 31, that number is 41.95 points.
We will use a team’s goal difference, league average points, and an exponent to find each teams xPoints. The formula is:
=(0.6455*Goal Difference)+League Average Points. This gives us the following:
Method #2 is a bit more involved and will require you to find some numbers. They are formulas we have used before so I won’t post them here. We need to find the differentials for the following:
- Shots Wide (Shots off Target for minus against)
- Shots on Target (SOT for minus against)
- Goals (Goals for minus against)
The formula itself is (Goal Difference * 0.47) + (Wide Shots Difference * 0.01) + (SOT Difference * 0.054) + League Average Points.
Alright, in Part VI we’ll take a look at improving our xGoal numbers and take a look at finding odds for Asian Handicaps. As usual, any questions feel free to contact me below or @petermckeever