PREDICT Football Matches with the POISSON Distribution | Excel Beginner Tutorial

Introduction g'day loans this video will explain how to take football standings and then transform it into a predicative model with expected goals and win probabilities using the poisson distribution in Excel Lads if you're interested in delving deeper into sports modeling or you're seeking some personalized guidance feel free to reach out to me via email or Twitter DMS to inquire about some paid one-on-one Zoom coaching sessions that I offer they're incredibly successful and in hot Demand right now so you can email me at lads.xl gmail.com or you can message me on Twitter where my handle is Excel underscore lads Calculating Attack and Defense Ratings all right lad so the first step in making a poisson model to predict matches is to get your League's standings and then grab each team's attack and defense ratings from this to do this we're going to first find the average goals scored by a team in a game played and the formula for this is the sum of the goals four divided by the sum of the matches played C2 to c21 when we press enter and reduce this figure to two decimal points we can say that on average a team scores 1.42 goals a game in this competition next I'm going to create two new columns called ATT rating which is going to list every team's attack rating under it and Def rating which is going to list every team's defense rating under it first we'll derive Manchester City's attack rating by dividing their goals four by their matches played and then once we do that we'll put this in Brackets and divide this total figure by The League average for goals scored in a game which is down here in cell G23 hit enter and you'll get 1.84 but we're not done yet because we want to be able to drag this across and down for every team so I'm going to Absolute references G23 so it doesn't move shortcut for that is command T if you're on Mac so put the dollar sign in front of the row and column and then for the matches played I want this to move up and down because not all the time is a team going to have 35 matches played you can see Brian only have 34 so the figure will change uh we're going to just absolute reference the column so dollar fine dollar sign in front of the C and now we're able to drag across and down to get Manchester City and the rest of the teams attack and defense ratings all right so now to interpret these ratings Manchester City's attack rating of 1.85 means that they score an average 1.8 1.85 more goals in a game than the average team so 1.85 goals for every one goal scored by The League average team the defense rating of 0.62 means they concede only 0.62 goals for every one goal conceded by The League average team obviously when looking at the other clubs and the formula in general an attack rating of over one is good whereas a defense rating of over one is bad because you want to concede less goals than the average team therefore defense rating of under one is good Naming Ranges now let's we're going to quickly name some ranges so that it'll be easier for us to reference them later on in formulas our first range we're going to name is going to be the list of teams and I'll just go up to the top left box over here and name it teams press enter and now our Ranch has been named so next I'm going to name the league average goals just average do it again in the top left hand box over there for the attack ratings I'll just name this range ATT press enter and I'll do thing do the same thing for the defense ratings but name it d e f again in this top left hand box Expected Goals the next step in our model Lads is to calculate the expected goals for each match up in the league for example if Manchester United would apply Chelsea how many goals are united and Chelsea expected to score against each other in this match so to do this create a new worksheet and in the top left hand corner over here I'm going to merge and center some cells and then copy and paste the merge cells below again and here I'm going to just type in the headers home and away so on top in this first cell after I format them home colon let's do that now home colon and then below that and so a three away column great next to these Souls I want to create a drop down list so that I can choose the home team and an away team in a hypothetical map shot so I'm going to merge and center these cells to the right and very quickly I'm going to format them so that they look nice and neat and readable you can put a border in between them and so now I want to make a drop down list for each cell to do this you need to select or highlight both cells go into the data tab up here and then over to the right there's this little icon that reads data validation you're going to click on that and then a pop-up window looking exactly like this will appear we want to create a list so under allow I'm going to select list from the drop down for our source that's just going to be equals teams because we want to be able to choose a team from our list teams which we named 30 seconds ago after you've pressed OK when you want to select a single cell there'll be a drop down button in the top right hand corner of the cell and then from here you can select the home team I'll select Manchester United and I'm going to format these cells a bit better and for the away team I'll select Tottenham now let's I'm going to merge and center these cells to the right again and within these cells will be our formula to calculate the expected goals for the home and away teams a basic formula for the home team's expected goals will be the home team's attack rating multiplied by the away team's defense rating multiplied by The League average goals per game to do this Lads I'm going to use an X lookup function so if you don't have access to this function in your Excel package you'll have to use the vlookup equivalent so Lads this is the formula for the home team equals x lookup Open Bracket our lookup value is the home team C1 comma our lookup array we're finding our home team in our list of teams a return array we want to return the home team's attack rating so comma ATT now we want to multiply this by the away teams defense rating so X lookup we want to look up the away team in this case Tottenham and C3 we want to look them up in teams and return their defensive rating so d e f close that bracket we want to multiply by The League average goals per game so multiply average and that's because we named all our ranges about a couple minutes ago so as I format these and round the expected goals to two decimal places you can see according to our model Manchester United is expected to score 1.68 goals against Tottenham let's do the same thing for the away team now equals x lookup we want to look up the away team we want to look up the away teams attack rating so comma teams comma ATT for their attack rating multiplied by the Home team's defense rating we're looking up C1 in our list of teams and we're returning their def defensive rating multiplied by average which is of course the league average goals per game and we can see Manchester United is slightly favored in this matchup if we change around the teams using our drop down list then the expected goals will obviously change according to the relative strength between the teams a better team will have a higher expected goals than a worse team Poisson Distribution our next step Lads is to find the probability of each team winning according to these expected goal values and to do this we're going to use something called the poisson distribution so I'm going to create a banner underneath our expected goals here titled poisson distribution and then we can format this Banner by Merchants centering these cells typing in poisson distribution and then underneath this Banner I'm going to write the header goals in column A and then I'm going to list the numbers 0 through to 10 representing zero goals all the way through to 10 goals in a single match now I want to get a column header in column b or cell B9 that'll read the home team goals so Manchester City whatever the home team is and goals so the formula for that is equals C1 and symbol shift 7 speech mark space goals speech mark press enter and you should end up with Manchester City goals for this Manchester City Everton match up so I'm going to do the same thing for the away team now so I want to get the away team so the UA teams Everton here I want to get Everton space goals that's going to be equals C3 and speech mark space goals speech mark press enter and now we've got Manchester City goals and Everton goals as column headers in B and C now LEDs in the home teams column we want to find the probability that Manchester City will score zero one two all the way through to 10 goals in a match against the away team selected which is Everton to do this we use a function called the poisson distribution which we're going to use right now to find cities probability of scoring zero goals in this match so equals poison dot dist that's poisson distribution Open Bracket X now that's just going to be the amount of goals where testing cities probability of scoring comma mean the mean is Manchester City or the home team's expected goals in i1 comma and the cumulative just type in false in capital letters we're not done yet though I want you to Absolute reference the i1 because when we drag down to find Manchester City's probability of scoring every goal from zero three to ten we want that expected goals to say fixed so when we auto fill this change it to a percentage to two decimal points we can see cities percentage chance of scoring every goal from zero through to ten zero goals have probability of five percent one goal sixteen point two four percent all the way through to ten goals which is unlikely at point zero six percent uh the most likely amount of Gold city or score is two at twenty three point three three now it's time to do the same thing for the away team so poisson dot disk that's a poisson distribution function X we want to find the probability Everton scores zero goals comma mean that's Everton's expected goals in sell that's going to be cell I3 for us comma then our cumulative will be false again make sure to Absolute reference the I3 that shortcuts command t on Mac press enter and then we can drag this down and we can see Everton's expected goal probabilities very different to Manchester cities due to the different expected goals between the two teams Everton have a much much higher chance 57 chance of scoring one goal and then as they score more and more goals that becomes more and more unlikely now we can do a little test to see if we've entered the formula in right the sum of the zero three to ten goals should be a hundred percent and that's right for Everton but you'll notice for Manchester City it's 99.98 that's nothing to worry about that's simply because we haven't accounted for goals above 10 so if we type in 11 and 12 that'll update the formula and we can see the rest of that 0.02 percent is accounted for The Next Step Lads is to come over to column e and type in the formula equals home team and speech mark space win percentage and then we're going to close that speech mark and as you could probably guess under this column we're going to find Manchester City's probability of winning at every goal scored let's do the same thing for column F we're going to put equals the away team at C3 and speech mark space win space mark percentage speech mark enter just the column size or width for that and now we've got Everton and Manchester City's win percentage so we're going to do Manchester City first and what's going to happen is we're going to find the probability Manchester City will win scoring one goals two goals three goals all the way through to 10 goals obviously they can't win scoring zero goals so we're going to start with one and the formula is going to be equals b11 so City's probability of scoring on goal multiplied by the sum of evertons C10 to C10 and we're going to Absolute reference the first C10 here so absolute reference that by pulling putting a dollar sign in front of the row and column and for the scenario of Manchester City winning with one goal that's going to be equal to about 9.33 I'll change this to percentage sign with two decimal places and as we drag that down because we absolutely reference that that first um C10 that C10 will stay constant and then the second C10 in our sum formula will adjust so that the probability of City winning scoring four goals is b14 multiplied by the sum of C 10 to C13 which is the sum of evidence growing zero one two and three goals because Man City can win four three four two four one or four nil let's do the same thing for evidence when percentage now starting with one goal of course equals C11 multiplied by the sum of B10 to B10 and we're going to Absolute reference the first B10 here by putting the dollar sign in front of the row and column as always press enter and Everton have a 1.8 chance of winning if they score a One Singular goal in a match and the highest probability of Everton winning actually involves them scoring two goals instead of one so that's something interesting you can take away from the formula and let's the final step of this poisson distribution method is to find the probability of the home team winning the away team winning and lastly the probability of a draw we're going to calculate these probabilities up in the merged cells we have created here to the right of the expected goals and it's really really simple to find the Home team's probability of winning the match Lads all you have to do is sum the win percentages we just calculated in column e so it's going to be equals sum E11 to E20 close the bracket press enter and after I format this real quick for you Lads you can see Manchester city are the overwhelming favorites to win the match at 84.48 it's the same process for the away team just sum the probabilities in column F under away team or Everton win percentage which is some f11 to F20 and Everton have a four percent chance of winning this match the probability of a draw is really intuitive it's just one minus the sum of both teams chances of winning so I'm going to create a new merge cell to the right of the win percentage for the home team and the way team and the formula to find the draw percentages is just one minus the sum of the 84 and 4 while the home team with percent and the away team win percentage and that's 10.89 percent so when you highlight these cells the sums should be equal to exactly 100 as you can see at the bottom of the screen and now using our drop down list we can actually change the teams to see different matchups and their effect on win probabilities so if I go over to the home team we can change it to Liverpool and Liverpool have a decreased chance of winning versus Manchester City against Everton Brentford haven't even lower percent chance and as we go further down the ladder let's go Leeds United the probabilities start evening up so it's 40 to 33 now and the weakest team in the league right now Southampton 29 so Everton are favorites to win this match and they have a higher expected goals too Heatmaps alright Lads now we're going to create a heat map of the correct score lines and their probability for each match up so Lads what we're going to do is go down to cell F22 where you're going to make that equal to the home team so equals c one press enter and that shall come up as Southampton great and then starting from cell b23 and going across ways you're going to list the numbers 0 through to 10 which will represent the number of goals the home team scores so zero one two three four five six seven eight nine ten I've just typed that in manually and now you can make that bold because that's sort of going to be a head up going down from cell B24 downwards we're going to type out the goals 0 to 10 again but this time the goals will represent how many goals the away team scores and to signify this in cell a28 you're going to make that equals to the away team or equals to C3 great make that bold and you can format that to look a bit nicer if you want in the middle of the cell and adjust the width so in this grid we want to calculate each and every score Line's probability of occurring to do this for the score line 0 to 0 we use the X lookup function and multiply the home team's probability of zero goals scoring zero goals by the away team's probability of scoring zero goals so you're going to do equals x lookup Open Bracket now our lookup value that'll be zero goals for the home team so that'll be cell c23 great comment our lookup array we're going to be looking that up in our list of goals from a 10 to a20 comma and our return rate we want to find we want to return rather southampton's probability of scoring zero through to 10 goals that's B10 to B20 and we're going to Absolute reference eight a 10 to a20 as well as B10 to B20 and you're going to only reference or absolute reference the row 23 so dollar sign in front of 23 rather than the C and then when we round this well change this to percentage form and round this to two decimal places we can see Southampton have a 39.01 chance of scoring zero goals now of course we have to multiply this by Everton's chance of scoring zero goals which is 31.82 percent so we do this by just multiplying this x lookup function by another Excel cut function this time our lookup value is cell B24 this time absolute reference just the column not the row because we're going downwards with evidence goals so dollar sign B24 our lookup array absolute reference a 10 a20 and then we're returning C10 to C20 absolute reference which is evidence when probabilities and we can see a nil or Draw in a Southampton versus Everton game has a 12 chance of happening now we Conditional Formatting can drag that formula down and across to see a matrix of when probabilities for every single score line this whole sum should be equal to or very close to 100 as you can see at the bottom there but next though I'm going to make this table more visually appealing so that it's easier to tell for example in this game and most likely score lines involve a lower amount of goals and to do this I have to highlight the whole table like this click the drop down list on conditional formatting over here select color scales and I'm just going to click on this first option that highlights the most likely score lines in green and the less likely score lines in red like so now you can see the most likely score line is a one nil Everton win with a 14.2 percent chance of happening and the most unlikely is a 1010 draw which has to two decimal places zero percent now I'm going to make the model a bit tighter so I'm going to swap the locations of the heat map and the calculations by highlighting the entire range and just dragging and dropping don't worry won't change any formulas or values and now that's our basic poisson XL model is finished so I'm going to change the home team from Southampton to a stronger team in Tottenham and you can see its effect on the heat map these score lines have highlighted over here greener in favor the home team more and that's the same again when I change it to the strongest current team in the league which is of course Man City we can see that the most likely scoreline is a two nil three nil win again Lads if you're interested in reaching out to me about some one-on-one XL Sports modeling lessons you can get in touch with me through my email which is lads.xl gmail.com or you can DM me on Twitter at Excel underscore lads