Hi lad, the problem with ELO Ratings is that they are only meant for win/loss scenarios like chess or knockout tournaments. So generating scores with ELO is less efficient versus the ATT/DEF method, and would require a custom function to scale a team's probability of winning to an expected goals figure 💪
Brilliant piece of work. Just one thought. Would it be even more realistic if you did teams goals for and against separately for home and away matches to work out two different averages?
Hi lad, the data collected in the ‘Historical Championship Stats’ worksheet was collected by myself. I recommend you just copy these values down in your own model, but if you want to collect data from previous years then I explain the formula briefly within the vid
Excellent video. I learned a lot from watching them. I do have a question. I may be interested in doing an Excel sheet like this one but for the next level English Championship League. For promoting teams from League 1 the rating calculation will be similar to the three promoted teams from ECL to EPL. However, how would handle the ATT and DEF calculation of the three newly relegated EPL teams in ECL? Again thanks!
Hi lad, thanks for watching 🙏 It would be the same process, but reversed. You would collect the ATT & DEF EPL Ratings of the relegated EPL teams in the previous 10 years. Then, collect their following Championship ATT & DEF Rating the next year. Through the trend function, you'll be able to then equate a team's EPL Rating to a Championship Rating. I hope that helps 🔥
I must commend you for your efforts, I just started following your coaching strategies yesterday and I am thrilled by your work. Please I want to ask some questions... How can one carefully predict exact or goal ranges of a football match?
Hi lad, thanks for watching 🙏 I suggest you have a look at the Poisson Distribution video on my channel. Upon building this model, you’ll have the probability of every scoreline. From here, you can work with the data to find the probability of certain events. For example, the probability of under 1.5 goals in a match is equal to the probability of a 0-0, 1-0 and a 0-1 scoreline between the teams 👍
Really interesting. I'm using Office 2019, so I have managed to substitute other formulas, e.g. Index/match or vlookup instead of xlookup, Rank instead of sort, etc. Have you thought about how this could be used in-season? Currently I'm placing in results as they come in (so the fixtures only simulate future fixtures), but thinking how to take into account actual in modifying the ATT/DEF as the season goes along. Maybe I'll see if some of your other videos might be of use with this.
Hi, I was wondering why you reverted the attacking and defensive strengths 25% towards the mean. I would have thought the uncertainty to do with injuries/roster changes at the start of the season would be accounted for in the data for the 21/22 season that you used to derive the attacking and defending strengths? But please correct me if I’m wrong! Could you also explain the formula you used to revert the att and def strengths towards the mean?
The Poisson distribution is well known for being able to provide pretty good predictions of football matches (not enough to beat the bookies though). Since the Poisson distribution is a special case of the binomial distribution, I suppose that was the reason for using it. But I let the Lad master confirm or totally disagree with my assumption. ;) Cheers
Thanks lad 🔥 The BINOM.INV function is very similar to the Poisson distribution, except that it places a limit on the amount of goals that is possible. Technically, there is no limit to the amount of goals a team can score under the Poisson distribution. The BINOM.INV function in the model has 10,000 trials. The 'probability_s', is essentially the probability of a trial's success; this is calculated as the xG / no. of trials. The alpha criteria is the RAND() function, which simulates the result. For example, if Man City have an xG of 2. The formula will run 10,000 trials, and the probability of a success in a trial is 2/10000, or 0.02%. However many successes there are is the number of goals in the simulation. For example, 3 successes would mean Man City score 3 goals in that simulation. In theory, the highest amount of goals Man City could get with the Binomial Inverse formula is 10,000 (although the probability of that is virtually nothing). This 'cap' is what allows Excel to come up with an inverse function, that inputs an xG figure and spits out a simulated result. As the Poisson distribution has no 'cap' or upper limit theoretically, Excel is unable to have a POISSON.DIST.INV function. I hope that makes sense lad 💪
@@excel_ladz thanks for the detailed answer ! I build once something similar in R using either double poisson for sampling/fitting or bivariate poisson, but it's not that easy to train. Do you thing it's possible to use a custom function to sample goals like dixon coles for adjusting draws in excel? I live using excel but sampling from a difficult model is challenging and perhaps needs vba.
@@excel_ladz There's no native inverse Poisson function in excel but the "Real Statistics Resource Pack" provides such function and many others. It's a third party package that has to be installed in excel but AFAIK it's free and legit. Cheers
It might be, but unfortunately I haven't explored this type of model yet in Excel. From personal experience, there's been plenty of models I thought were only possible with VBA, but Excel eventually found a way - especially with the new array functions introduced. I know this is very broad, but I think custom adjustments to probability distributions such as the Dixon Coles method are very interesting and they are something I'll definitely look into in the future 🔥
Not in too much depth lad. I probably won’t be making videos with a lot of VBA in the short term, as it’s not very user friendly (to the beginner at least) 👍
Fantastic video! If you were to add to the “analysis” page where it would say percentage of finishing in the top 4 or relegated instead of just finishing 2nd or 3rd would you just add them together or is there a more complicated formula to calculating that?
G'day lad, yes there is 👍 Essentially, instead of using the SORT function you have to rank teams by a custom 'Ranking Points' formula. For example, adding Points*1000 + Goal Difference + Goals Scored*(1/1000) will make sure teams are ordered in Points, Goal Difference and Goals Scored order once a RANK function has been applied to it. I'm uploading a video very shortly that will feature this method 🔥
@@excel_ladz Sort works with your "Ranking Pts", Simulation works fine , problem is with the Countif function at 24:06 because of the "Analysis!$B$1#/1000" again an excel 365/2019 issue I cant afford to upgrade to 365 Can you pls find a workaround Cheers Brian
@@myarnie1950 Instead of $B$1#, you would just write $B$1:$U$1. The # is for arrays, and so for Excel 2019 you would need to reference the range instead. I hope that helps, and it should be the same workaround for other spots in the video 👍
G’day lads, comment below if you have any questions! 💪
Bro I was legit looking for something like this days before you posted it ahah. Glad I found it now great vid mate
This is good but it’s basically a simulation from last years results
Excellent! I´m always looking forward to watching your videos 💯👌
I’m especially interesting in various kinds of simulations that generate scores. What about one using ELO? Could that be possible?
Hi lad, the problem with ELO Ratings is that they are only meant for win/loss scenarios like chess or knockout tournaments. So generating scores with ELO is less efficient versus the ATT/DEF method, and would require a custom function to scale a team's probability of winning to an expected goals figure 💪
GOAT of Excel
Great to see another video. Thanks!
Remarkable work as usual! Thanks!
Brilliant piece of work. Just one thought. Would it be even more realistic if you did teams goals for and against separately for home and away matches to work out two different averages?
You make it look so easy 😫
Thanks you for the amazing work. It's appreciated a lot. I have a single query on where you get the historical data
Hi lad, the data collected in the ‘Historical Championship Stats’ worksheet was collected by myself. I recommend you just copy these values down in your own model, but if you want to collect data from previous years then I explain the formula briefly within the vid
Excellent video. I learned a lot from watching them. I do have a question. I may be interested in doing an Excel sheet like this one but for the next level English Championship League. For promoting teams from League 1 the rating calculation will be similar to the three promoted teams from ECL to EPL. However, how would handle the ATT and DEF calculation of the three newly relegated EPL teams in ECL? Again thanks!
Hi lad, thanks for watching 🙏 It would be the same process, but reversed. You would collect the ATT & DEF EPL Ratings of the relegated EPL teams in the previous 10 years. Then, collect their following Championship ATT & DEF Rating the next year. Through the trend function, you'll be able to then equate a team's EPL Rating to a Championship Rating. I hope that helps 🔥
I must commend you for your efforts, I just started following your coaching strategies yesterday and I am thrilled by your work.
Please I want to ask some questions...
How can one carefully predict exact or goal ranges of a football match?
Hi lad, thanks for watching 🙏 I suggest you have a look at the Poisson Distribution video on my channel. Upon building this model, you’ll have the probability of every scoreline. From here, you can work with the data to find the probability of certain events. For example, the probability of under 1.5 goals in a match is equal to the probability of a 0-0, 1-0 and a 0-1 scoreline between the teams 👍
Really interesting. I'm using Office 2019, so I have managed to substitute other formulas, e.g. Index/match or vlookup instead of xlookup, Rank instead of sort, etc. Have you thought about how this could be used in-season? Currently I'm placing in results as they come in (so the fixtures only simulate future fixtures), but thinking how to take into account actual in modifying the ATT/DEF as the season goes along. Maybe I'll see if some of your other videos might be of use with this.
Hi, I was wondering why you reverted the attacking and defensive strengths 25% towards the mean. I would have thought the uncertainty to do with injuries/roster changes at the start of the season would be accounted for in the data for the 21/22 season that you used to derive the attacking and defending strengths? But please correct me if I’m wrong! Could you also explain the formula you used to revert the att and def strengths towards the mean?
Do it again for the 2024/25 season after the Championship Final has been played
You are on fire, thanks! Why did you use binomial to sample goals?
The Poisson distribution is well known for being able to provide pretty good predictions of football matches (not enough to beat the bookies though). Since the Poisson distribution is a special case of the binomial distribution, I suppose that was the reason for using it. But I let the Lad master confirm or totally disagree with my assumption. ;) Cheers
Thanks lad 🔥 The BINOM.INV function is very similar to the Poisson distribution, except that it places a limit on the amount of goals that is possible. Technically, there is no limit to the amount of goals a team can score under the Poisson distribution.
The BINOM.INV function in the model has 10,000 trials. The 'probability_s', is essentially the probability of a trial's success; this is calculated as the xG / no. of trials. The alpha criteria is the RAND() function, which simulates the result. For example, if Man City have an xG of 2. The formula will run 10,000 trials, and the probability of a success in a trial is 2/10000, or 0.02%. However many successes there are is the number of goals in the simulation. For example, 3 successes would mean Man City score 3 goals in that simulation.
In theory, the highest amount of goals Man City could get with the Binomial Inverse formula is 10,000 (although the probability of that is virtually nothing). This 'cap' is what allows Excel to come up with an inverse function, that inputs an xG figure and spits out a simulated result. As the Poisson distribution has no 'cap' or upper limit theoretically, Excel is unable to have a POISSON.DIST.INV function. I hope that makes sense lad 💪
@@excel_ladz thanks for the detailed answer ! I build once something similar in R using either double poisson for sampling/fitting or bivariate poisson, but it's not that easy to train. Do you thing it's possible to use a custom function to sample goals like dixon coles for adjusting draws in excel? I live using excel but sampling from a difficult model is challenging and perhaps needs vba.
@@excel_ladz There's no native inverse Poisson function in excel but the "Real Statistics Resource Pack" provides such function and many others. It's a third party package that has to be installed in excel but AFAIK it's free and legit. Cheers
It might be, but unfortunately I haven't explored this type of model yet in Excel. From personal experience, there's been plenty of models I thought were only possible with VBA, but Excel eventually found a way - especially with the new array functions introduced. I know this is very broad, but I think custom adjustments to probability distributions such as the Dixon Coles method are very interesting and they are something I'll definitely look into in the future 🔥
Please do Australian Football (AFL)
I’ve seen something called VBA excel that automates everything. Do you know how to do that?
Not in too much depth lad. I probably won’t be making videos with a lot of VBA in the short term, as it’s not very user friendly (to the beginner at least) 👍
Great job! Thank you! Is there a reason why the reverted att and def value is 25% and not other number? Thank you.
Can you make another one for the 2024/25 season please
Hey i know this is late but is there any way to sim f1 for 1000 times?
Fantastic video! If you were to add to the “analysis” page where it would say percentage of finishing in the top 4 or relegated instead of just finishing 2nd or 3rd would you just add them together or is there a more complicated formula to calculating that?
Hi lad, you could definitely add them together. A better formula would be to add in a criteria like “
@@excel_ladz thanks man. That’s probably easier. Good video from you again.
Do you think there's a chance you do a nhl game predicting one?
I hope so lad 🔥 It would be the same concept, except there would be no need to come up with ratings for promoted/relegated teams 👍
Now 9:20 not working why why why I just want to sim 2024/25
why isnt it working 6:00 this doesnt work but 6:30 does work help me pls
it works
places . not ,
hello nice video, i am having a problem around 24:00 the simulation results brings 0 value, that COUNTIF function
Its working now cheers!
please can you post the 2024/25 season i want to compare the data, thx
Can you do one for NFL season upcoming possibly
I'll definitely release an NFL video, hopefully soon lad 👍
Great work, is there a way to Sort the positions using excel 2019 at the 18.05 part
G'day lad, yes there is 👍 Essentially, instead of using the SORT function you have to rank teams by a custom 'Ranking Points' formula. For example, adding Points*1000 + Goal Difference + Goals Scored*(1/1000) will make sure teams are ordered in Points, Goal Difference and Goals Scored order once a RANK function has been applied to it. I'm uploading a video very shortly that will feature this method 🔥
Brilliant
@@excel_ladz Sort works with your "Ranking Pts", Simulation works fine , problem is with the Countif function at 24:06 because of the "Analysis!$B$1#/1000" again an excel 365/2019 issue
I cant afford to upgrade to 365 Can you pls find a workaround Cheers Brian
@@myarnie1950 Instead of $B$1#, you would just write $B$1:$U$1. The # is for arrays, and so for Excel 2019 you would need to reference the range instead. I hope that helps, and it should be the same workaround for other spots in the video 👍
I worked around it and increased the trial to 2500 which made a massive difference to team placing Thanks for your help with a brilliant project
impressive!
Could you make simulation video for champions league?