PREDICT Football Matches with the POISSON Distribution | Excel Beginner Tutorial

[@excel_ladz]

G'day lads, if you want access to this model (and every other Excel LADZ model), join the Excel LADZ community! Sign up here: www.patreon.com/ExcelLADZ 💪

[@UchennaChibuike-mc9vj]

Pls how do I use the vlookup function.... I need the formula

[@-Tharos-]

Thanks for the tutorial. As some Lads already commented, this model can be further improved, maybe material for new videos: 1 - use home and away figures to calculate teams' attacking and defensive strengths. 2 - Use xG figures instead of real goals to feed the poisson distribution. This one is debatable, but usually people say it's better to use xG instead of goals. One just have to find a source of xG online (FBref, Understat), which means extra work, maybe involving some scrapping. 3 - Poisson Distribution is not very good at predicting low scores, so some kind of correction factor must be applied: extra work again. :) Anyway, for starters this is a very good video tutorial, keep them coming! Cheers

[@excel_ladz]

Thanks lad, I appreciate your support 🔥 Yes, a more intermediate Poisson model would take into account a home team advantage and likely import xG stats from FBref using a power query. I’ll hopefully be able to release something including this in the short term lad 💪

[@YaNykyta]

Using Home and Away figures to calculate teams' attacking and defensive strengths is more accurate for sure. I am monitoring both for long time. But... xG figures instead of real goals to feed the poisson distribution is definitely bad idea. Simple, I am not fan of xG because that parameter is much worse than real goals from real team history.

[@-Tharos-]

@@YaNykyta xG has been "sold" as a better indicator of a team's performance but, yes, it's debatable. I'm not as fan of xG nowadays as I was when I wrote my previous comment. Sometimes I look at xG figures after a match and it doesn't seem right, all things considered. A team may create a lot of "heat" in the opponent box without actually shooting; In this situation xG=0, but there was definitely threat. So maybe xG doesn't tell the whole story and neither do the actual goals. Football is one of the hardest sports to predict, there's a lot of randomness. Cheers

[@YaNykyta]

@@-Tharos- Great pleasure! Always nice to meet reasonable people. Even virtually.

[@YaNykyta]

@-Tharos- Yesterday matches (Home/Away Poisson Mode): FC Heidenheim vs. SC Freiburg - 3:2 (0:1) Expected Goals: 2.51 vs. 0.65 Bookie (fair odds) chances: TG Under 2.5 = 49.1% and TG Over 2.5 = 50.9% Bookie (fair odds) Money Line: HW 32.75% Dr 28.16% AW 39.09% Poisson Money Line: HW 73.76% Dr 14.59% AW 7.61% Incredible disbalance! I betted Home Win. What do you think?

[@MarkWilson-bx9gg]

Enjoyed this video and others from your soccer range. Do you know if there is a way to calculate which team is most likely to score first goal.

[@excel_ladz]

Thanks lad 🔥 It’s actually a pretty complex answer to that one, and it can’t be directly worked out from the info in this video. What you’d have to do is split up the game into parts. For example, 90 mins * 60 secs equals 5,400 seconds in a game. Then what you could is divide each team’s xG by 5,400 to get their xG for each second of the match. Then you could simulate the 5,400 parts of the match using each team’s modified xG. When analysing the simulations, the team who scores the first goal of the match is recorded (or none of no goals are scored). Then, using a Data Table, you would run this simulation say 1,000 times to get a probability for each team. I’ll probably make a video on this eventually 💪

[@lunarium5530]

Hello, I really like your videos! Keep up the good work! I have a question regarding the expected goal calculation. Can you explain why and how the formula to calculate the expected goal is used?

[@excel_ladz]

Absolutely lad 💪 Basically, the Expected Goals figure is obtained through the formula: Team’s Attack Strength * Opposition’s Defence Strength * League Average Goals. The ATT (Attack) Rating for each team is their relative strength compared to the league average team. For example, if 1.5 goals per match are scored per game on average, then a team averaging 3 goals a game would have an ATT Rating of 2. Obviously, an ATT Rating above 1 is good. It’s the same thing for the DEF Rating, but of course, a rating of BELOW 1 would be good. The league average goals figure just refers to the average number of goals scored in a match in the competition. How that helps lad 🔥

[@mikemac2824]

I love this video. Very easy and explainatory. Just a question....this uses the previous season stats. When would you start plugging in current year stats? after 5 games? 10? 20?

[@footballratingsspreadsheet1372]

Great video... ty Can you just confirm that we don't need to manually update the league table after every game... we can just click on refresh data (if imported from Web, I mean)

[@excel_ladz]

Hi lad, thanks for watching 🔥 If you use a power query to import the EPL Table to Excel, then all you have to do is click on refresh data and it will update automatically 👍. Of course, if you only copy and paste the data into Excel, you must update the data every round. I hope that answers your question lad 💪

[@Rasmus98]

Fantastic video lad!

[@Rasmus98]

On the ATT and DEF ratings, could you divide them to get an overall rating or how could I do that? I need something to predict a game when it’s not home or away. Just the overall quality of a team. Hope you can help. Thanks a lot.

[@excel_ladz]

Hi lad, an idea would be to simply multiply a team’s ATT Rating by the reciprocal of their DEF Rating (or 1 / DEF Rating). Sorting all the teams by this metric would give you a simplified ‘Power Ratings’ system. Hope that helps lad 🔥

[@jlwest899]

If you include different variables such as home/away form, shots on goals, average possession, total shots etc. Could you refine the att and defensive strengths to provide a more accurate outcome? Love the content btw!

[@jlwest899]

I also wonder if you're able to add more weight to recent games/form i.e. how they've played in the past 2 months.

[@excel_ladz]

G’day lad, thanks for watching ✅ A model taking into account different stats as you have mentioned would involve the process of using regression on Excel to calculate more accurate ATT/DEF Ratings. In terms of taking into account form, you could attach some sort of importance multiplier to the most recent games a team has played, and then use these figures to calculate a weighted attack and defence rating lad 🔥

[@jlwest899]

Thanks for the response lad - Keep up the good work!

[@davemason2604]

Great videos and I have followed all tutorials. One extra part could be if for some reason you chose the same team for home and away a message could display saying Please chose another team by using an IF statement. Also is there an ELO rating or something similar for Rugby League as I prefer Rugby League to Football?

[@vincentwhozu9724]

Can you add the halftime score and full-time score on a separate table mate, also if you can include over/under goals probabilities and BTTS it would look OK mate Thanks.

[@excel_ladz]

G'day mate, I'll definitely do a separate video that covers half-time scores. The process is really easy, it basically involves halving the expected goals and then using the Poisson Distribution in the same manner (as seen in this video). Furthermore, there'll be a video using this for sports betting including over/under goal probabilities 💪

[@vcbahia1000]

@@excel_ladz , Hello! Parabéns pelos vídeos. Aguardo pelos próximos. Obrigado!

[@jorisaboughe9631]

The next part

[@IGNT_]

Have you tried the technique ? And did it work for you?

[@ftwgamer710]

Great video. It was really helpful for me. Do you know how can i get the league's standings into EXCEL?

[@smsonline5115]

when i was using the =games formula in excel from the elo ratings video it kept printing the first match from cell A4 to the bottom can check if it is correct pls =IF(Games!$D$11=0,"",Games!$A$4:OFFSET(Games!$A$4,Games!$D$11-1,0)) also it would be nice if you did overs and unders for the elo ratings

[@markhamilton6171]

Hi. Good video. Excel can be fun. Did you have any simple way to do back testing? Maybe need previous results and what the odds were and do some kind of EV calculation.

[@excel_ladz]

G'day lad, thanks for the comment 🔥 I'm still in the process of making a model on Excel to properly backtest this strategy, and I'll release a video sharing how to do this when I'm done 👍

[@arturozapata4355]

Hello, excellent video mate, I was wondering if you coud share a PDF of the information of the video. I tend to learn better that way.please i

[@hasantoroslu3462]

Poisson modeli çok düşük bir modeldir tek avantajı güclü takım ile zayıf takım arasındaki attığı golden sana gösterir ama işte öyle değil örnek ev sahibi %85 gösteriyor deplasman ise%10 gösteriyor fakat maç berabere bitiyor %5 pay ile bu tamamen yanıltıcıdır bence hesaplamalar takımların ev sahibinin içerde oynadığı deplasmanında dışarıda oynadığı ve son 3 maçlarını ele alması ve ondan sonra bahis şirketlerin verdiği oran ile bir ortak değer çıkarması oyunın şansını ikiye katlar tabiki top her zaman kaleyi bulmaz teşekkürler bu bir öneri

[@SannyDavid7]

Richtig 👍🏼 weißt du vielleicht ein Modell der besser ist um es auch nachzubauen?

[@samuelwaring1981]

[@qbaliu6462]

Hi, great video. I have a question: Why does a team like Man City have a lower defense rating than Southampton? What is the definition of the defense rating? Do I understand correctly that the formula is (goals against / matches played) / average scored goals?

[@excel_ladz]

Hi lad, the lower the defence rating the better. For example, if Manchester City has a defence rating of 0.6, that means they concede only 0.6 goals for every 1 goal conceded by the league average team. As a result, a defence rating of under 1 is relatively 'good' for a league. Of course, on the other hand, an ATT Rating of over 1 is considered 'good'. Your formula looks correct 👍

[@qbaliu6462]

@@excel_ladz Thanks :)

[@wibjewellery9605]

Hi ur video is great,teachable...and i understand easly and i made my own model according to ur general model thanks a lot...beside can u make another video for home teams with home team AVG and for away teams with away team AVG....With their own attack and defense strength....pls pls..❤❤❤

[@excel_ladz]

Hi lad, thanks for the idea 🙏 If you’re able to follow this video, you should be able to build a model like that. All you need to do is separate a team’s home and away data, and then use their home rating when they play at home, and vice versa!

[@Victorino76]

Hi lad, thx for the incredible content, two points : could you provide us a model that valid or deny the % if we were betting with the model. How to use this model in the start of the season ? Should we use the datas from previous season ? Because i think we need 20/25 matchs to get datas of currently season. Thx for your answer.

[@excel_ladz]

Hi lad, thanks for watching 🔥 I’m actually planning to release a video on how to use this model at the start of the EPL season very soon 👍 You’re totally right, and because the start of the season does not have the 20 rounds of data needed, it is necessary to revert the ATT/DEF Ratings a certain percentage back to the mean. This means using last year’s ratings, but say for a 20% reversion to the mean, a 1.05 ATT Rating would become 1.04 💪 This is to account for the uncertainty created by squad changes. I hope this helps lad 🔥

[@Victorino76]

@@excel_ladz Ok i see what you mean. Thanks a lot for the answers i m eager to watch your next videos on this subject. Also, do you have a Telegram channel or other thing like that to communicate with other lads more efficiently ?

[@excel_ladz]

@@Victorino76 I have a Patreon community where I'm able to chat to every lad 1-on-1 through Discord. If interested lad, check it out here: www.patreon.com/ExcelLADZ

[@normansmith8004]

Hi can i ask a question? From the minute 1.20 you show how to sum up the attack ratings. But at 2.10 you drag across to Defence ratings, shouldnt defence ratings have their own ratings, not the same as attacking. Let me know if i am missing something please? Thanks, great video by the way.

[@excel_ladz]

Hi lad, the defence ratings are based off of the goals conceded. As a result, when I drag the ATT Rating formula to the right, the cell referring to goals scored in the formula shifts one cell to the right - to a team’s goals conceded. I hope that makes more sense 🔥

[@normansmith8004]

Hi, i understand that part, but there was no number in the def cell, for it to count, just a little baffled thats all?

[@normansmith8004]

Also when is it advisable to start using this system? meaning after 5 games, 10 games. Thanks for your time on this matter.

[@lellobarter]

excellent video

[@normansmith8004]

Brilliant, thanks.

[@Lovelymuhau7335]

Were and how do you download the EPL table

[@excel_ladz]

Hi lad! The website Football Reference provides a league table for the EPL, which you can bring into Excel through a Power Query. I hope that helps 👍

[@ohlawdy1986]

These videos are great! Do you have a tutorial on how to get the info in the table to update itself or have found an API that you can just refresh for this so you can do it live over the season? Also would it be possible to have the Raw Data for more than one league but still link it to the Results tab?

[@ohlawdy1986]

Amazing what this video has started, a day ago I didn't know how to do any of this and now I've made 2 workbooks, one that is the same as the video but has drop downs and so far 4 divisions and the second one I use Home and Away form so Home attack v Away Def and Away attack v Home def in stead and have done that for 2 divisions. So thank you :)

[@excel_ladz]

Hi lad, you could definitely link a Power Query into your Excel file in order to get live data. Football Reference (FBref) is a great website I use for this. When that's connected to your model, all you have to do is select 'Refresh All' in the Data tab, then your stats (and as a result your model's results) will update 👍

[@mrt2710]

​@excel_ladz hi I don't know much about excel. How do I connect the excel with API or data from website like soccerstats. This would really help.

[@ชาตรีวรรณสิงห์]

Can you share it? @@ohlawdy1986

[@shaunrigby1]

Hi Lad, what formula would I use to extract the most likely result from the heatmap

[@excel_ladz]

G’day lad, you could search for the maximum value using the MAX function. Select the entire range of the heatmap and then it will return the percentage value which is the highest. Then, using an XLOOKUP or VLOOKUP function, you could return the home and away team scores that correspond to the maximum value 💪

[@shaunrigby1]

@@excel_ladz nice one lad, i will give that a shot.

[@kefabosire]

This is Awesome

[@thatothito2793]

Can you use this information on the new 2023 2024 season

[@excel_ladz]

Yep, all you would have to do is adjust the stats to be trailing... For example, using only the first two rounds of data for games in Round 3 of the season is unlikely to be predicatively powerful. For instance, using the 2022/23 ATT & DEF data to begin the predictions for the 23/24 season. I'm planning on releasing this video next, and simulating the 2023/24 EPL Season 🔥

[@ifejikaanthony2746]

Why did you use full table instead of home table and away table? Will the model know the attacking strength of teams in home and away?

[@excel_ladz]

Hi lad, thanks for watching 🔥This is just a basic Poisson Model, and one of its drawbacks is that it doesn't take into account a team's advantage playing at home. You could absolutely adjust the model to take into home/away strength lad 👍

[@ifejikaanthony2746]

Okay! Your Model just predicted Tottenham Hotspur vs Brentford correctly

[@ifejikaanthony2746]

Hey! And how to get over 2.5 goals, under 2.5 and BTTS with this model?

[@excel_ladz]

@@ifejikaanthony2746 You would simply sum the probabilities. For example, under 2.5 goals for the game would include the scorelines 0-0, 1-0, 0-1 and 1-1. You would sum the corresponding probabilities of these scorelines to get your odds 👍

[@gokudesenho715]

THE PPOISSON DISTRIBUTION UNDERESTIMATES THE VALUES (0-0, 1-0, 0-1, 1-1), SOME OF THE MOST COMMON SCORES IN A SOCCER GAME, SO IT IS NECESSARY TO DEFINE A CORRECTIVE FACTOR AND APPLY IT TO THESE VALUES.

[@excel_ladz]

Hi lad, I do recognise that the Poisson distribution can often underestimate the chance of low-scoring games. This is most likely due to the inherent assumption of the Poisson distribution in that all goals (events) are independent of each other, which is obviously not realistic. As this is only a basic Poisson Distribution Model it doesn't include any corrective factors. However, hopefully in the next couple videos I can release some more advanced models 🔥 Thanks for watching lad 💪

[@SannyDavid7]

I need to learn this 😢

[@IanGriffin-w7b]

Does not take home advantage into consideration, how do I add that into my sheet? I tried adding 0.5 to the Att rating but doesnt make sense

[@excel_ladz]

Hi lad, you can add the home advantage to a team’s xGoals (Expected Goals) at the end of the calculation. For example, let’s pretend home teams have proven to score 10% more goals than away sides. You would multiply the xGoals of the Home Team by SQRT(1 + 10%). Then, you could multiply the xGoals of the Away Team by: 1/SQRT(1 + 10%). This would yield a total home advantage of 10% for the Home Team 👍

[@fishnice24762]

Amazing 👏

[@smsonline5115]

please where did you get the ratings from

[@excel_ladz]

Hi lad, the league table is simply copy and pasted from Google. All you have to do is search up 'English Premier League Standings', and then it should appear as your first result. In terms of calculating the attack and defence ratings, it's all contained within the video 👍 A team's attack rating is derived through dividing their goals scored by their matches played, and then dividing this total figure by the league average goals per game. The same process (but using goals conceded) is used to calculate a team's defence rating. Thanks for watching lad 💪

[@smsonline5115]

@@excel_ladz ok thanks man

[@SannyDavid7]

If I insert a new league table it will update itself ? please answer

[@excel_ladz]

Hi lad, yes it will 🔥 The formulas are based on cells, not values. So, if the cells are updated where the league table is, so will the predictions!

[@SannyDavid7]

@@excel_ladz thank you 🤗

[@jorisaboughe9631]

How to get the row stats ??

[@excel_ladz]

Hi lad, if you're talking about the stats at the 0:40 mark of the video, this is just the English Premier League Table. All you have to do is search 'EPL table' on Google, and it will come up as the first result. 🔥 If this doesn't answer your question lad, don't hesitate to comment again 💪

[@Sgsnippets]

How is this different from the elo ratings

[@excel_ladz]

G'day lad, the Excel LADZ ELO model is based on external ratings I have imported from a website I have no affiliation with. In this video, you can use the Poisson distribution to come up with a Win % yourself. Furthermore, you can use the model with any league or major sport you want (as long as you have a ladder or standings to use).

[@Sgsnippets]

Tnx Lad.

[@NVCardMaster]

Would this work on google sheets?

[@excel_ladz]

Yes lad, you could do this on google sheets 🔥 While the model is entirely possible to build on Sheets, often Google has slightly different syntax so be careful lad 💪

[@itumelengkonyana1099]

Caan you please re do but with excel 2016

[@neilwayman8769]

hi how an i download this file? cheers

[@excel_ladz]

Hi lad 🔥Here's a link to become an Excel LADZ Member, where you can download any and all models featured on the channel: www.patreon.com/ExcelLADZ

[@KDP-iw1ev]

can you do nfl and college football

[@ชาตรีวรรณสิงห์]

Can't use (Xlookup), please help.

[@olumidejamesomoshola851]

Yo lads have been trying to do this on excel 360 but am finding it hard pls who can help pls

[@SannyDavid7]

Sorry but many of the results were wrong🤧