EASIEST Model to PREDICT Football Matches With ELO | Excel Beginner Tutorial 

You should run a Blackjack simulation in Excel

@@williamjacksquilliam Sounds like an idea... Will take a lot of planning though, so not a video in the short term unfortunately.

Please when will you upload the video for Over or Under Basketball total points Prediction?

@@boldbuzz I'm glad you're eager lad, this will be published very soon in the coming weeks 💪 💪.

=IF(ISTEXT(C2)=TRUE, C2&" vs "&D2,"') THERE IS A PROBLEM WITH THIS FORMULA

I'm glad you liked the video lad! Your comment is very interesting. I have read about certain limitations of the Poisson distribution surrounding the low scores, so I'll definitely do some research surrounding the Dixon-Coles implementation. However, this will most likely take some time, so unfortunately it may not be a video in the short term.

Thanks lad, I appreciate the support. Usually I would definitely use a power query to import the data, which would update automatically in Excel. However, this video was more geared towards beginners, and at first the power query skill is a bit tedious and difficult to complete. Future Football ELO models will absolutely include a power query and automatically updated data lad, stay tuned 👍

What do you think about Google sheets

Hi lad, in my opinion Excel is much more powerful, however Google Sheets can provide Google users a very easy introduction to spreadsheets.

That's a shame. If I remember correctly, you were having a problem with the Pts Sim? I've got two new methods for you to try and solve your problem lad. Firstly, can I confirm you are using an English version of Excel? There was one subscriber who's issue with the formula was immediately fixed, because he was using the French version of Office, and so the BINOM.INV formula was written differently. Secondly, try breaking up the formula into parts. To simulate the 2 pointers, enter in: 2*BINOM.INV(BINOM.INV(F12,G12,RAND()),H12,RAND()) If this simulation works and changes every time you update the cells, then move onto the 3 pointers and Free Throws until the formula for Pts Sim. is complete. I hope this helps you lad, let me know how it goes.

@@excel_ladz yes US English

Morning: =if (d13>0,iferror(2*binom.inv(binom.inv(f13,g13,Rand()),h13,Rand()) is this correct...my data is on row 13

@@bobnicholson6350 Try entering this: =2*BINOM.INV(BINOM.INV(F13,G13,RAND()),H13,RAND()) for Row 13. This should spill out the simulated points for only 2-point shots for that player in Row 13.

@@excel_ladz Tried that but got #num! error...pasted numbers in different cells as Values and got same result.#NUM!...What to do??!!

I'm glad you won lad 💪 I would try and keep re-doing the process, trying to 'Refresh All' (found in the Data tab) as soon as the data is copy and pasted into the spreadsheet. Make sure you update the slicer 👍 I hope that helps lad, let me know if you have any other issues 🔥

@@excel_ladz It helped a lot thank you.

Of course lad, I’ll work hard so that the video can be uploaded within the next 7 days👍

Appreciate it lad. Great data makes life worth living.

For sure lad, this is coming soon. Make sure you’re subscribed 💪

Hi lad, thanks for watching 🔥 My first suggestion would be to ensure that the copied timeline in 10:10 of the video updates the 'games' range when you change the date. You can just switch back and forth between the 'Dashboard' and 'Games' worksheet to doublecheck this 👍 My second suggestion would be that when you select the 'list' in Data Validation, make sure the boxes 'Ignore blank' and 'In-cell drop-down' are both selected. This can be seen at 10:40 of the video. If none of these work lad, it may be due to your version of Excel not supporting dynamic ranges or arrays. It's odd though that Column B of the 'Games' worksheet works yet your Dashboard worksheet dropdown list is not. Let me know if that helps lad 😃

Yes lad, that'll be a priority heading into March. 👍

G’day lad, I would firstly find the most reliable site. There would be a million different ways to compile ELO Ratings, so use the most well established ELO website for the NFL. The formula to compile the win percentage based off of the ELO Ratings will differ with each system… While I don’t recommend you get the actual ratings from Wikipedia, it’s good for searching for mathematical formulas to transform the ratings into win probability. ELO Ratings only deal with win/loss scenarios. As a result, any expected goals calculations as well as draw probabilities cannot reliably be calculated. I know all this is probably common sense, but I hope it helps a bit lad 🤞

Hi lad, yeah that shouldn’t be the case. The sum of all probabilities for a match’s scoreline should be 100%, so there’s probably an error somewhere in your formulas (if the imported data is correct). Let me know if you have anymore specific questions lad 👍

G'day lad, I'm just using an XLOOKUP function to pull in the data values for each scoreline. If the stats we imported say that a 0-0 draw has a 0.121 likelihood, then I'm just grabbing that and plugging it into the dashboard to make it easy to read and understand 👍

G'day lad, thanks for watching! I usually use a power query on Excel to import data that updates automatically (so I don't need to copy and paste the data every day). An example of this is my NBA video where I import live stats to predict games. The reason I didn't do it for this particular video was because it was mainly geared at beginners who, without prior experience, would find obtaining the live date tedious and difficult. However, future videos on Football ELO Models will definitely feature live data lad 👍

@@excel_ladz cant wait for that!

game_ID looks like: =IF(ISTEXT(C2)=TRUE,C2&" vs "&D2,"")

Thanks

Hi lad, thanks for watching 🔥 You may not be doing anything wrong... Unfortunately later versions of Excel (e.g. Excel 2016 and later) don't recognise the XLOOKUP function. Is the #name error appearing on your dashboard? The solution for this would be to replace all the XLOOKUP functions in the formulas with a VLOOKUP function. I hope that helps lad 💪

All you'll have to do is swap the XLOOKUP function with VLOOKUP 👍

Will try that tomorrow. I couldn't get the list of games to appear when you typed =games around 9 minutes into video. It only shows one match when I do it. Not a list

Hi lad, I’ll try and give you a couple broad suggestions in the hope it’ll fix it for you. Wit regards to the percentages, you have to convert them yourself by selecting all the relevant cells, and then clicking on the percentage icon in the home tab. Once all your data is in, go to the Data tab and select ‘Refresh All’. This will make sure your pivot table updates, and you can then use the slicer to file yet games by date. If you’re using an old version of Excel (one that doesn’t support XLOOKUP), you’ll have to write the formulas with VLOOKUP functions 👍 I hope that helps lad

G’day lad, thanks for the support 🔥. You can reach out to me on Twitter. My handle is @excel_ladz

@@excel_ladz Hey, Lad. Thank you very much for getting back to me, I appreciate it.

Thanks for watching lad, make sure you're subscribed 👍 . In this video I use the XLOOKUP function to grab the home team. However, only Excel 2021 and the Microsoft 365 versions of Excel have access to this function. If you are using an older model of Excel, you must use VLOOKUP instead of XLOOKUP (it's a relatively easy switch between the two). I hope that helps lad 💪. If not, make sure you comment again and I'll get back to you as soon as possible.

Thanks for watching lad, I appreciate the support. With regards to your question, in the future I will be making a football video in which multiple parameters (e.g. possession, shots, passing, etc) are used in the model. And then from then, maybe rather than simulating the goals scored in the match, I can use the poisson distribution to solve for each team's chance of winning the match. I hope that helps lad.

@@excel_ladz That will be helpful waiting for the video

@@excel_ladz is there a way you can add an output of correct scores, highest and the second highest values with there corresponding correct score outcome and percentage? for example, probable outcome 3-2 14%..

@@reportspayment Hi lad, I do this at stage 19:30 of the video. Instead of spilling out the highest probable scoreline though, I use conditional formatting to highlight it for me. This is shown in 20:30.

G’day lad, unfortunately with the way the data is pulled, there’s no concrete way to backtest the model’s suggestions. However, you could keep a running ‘success rate’ of the model and track its success. Of this was helpful lad, please subscribe!

Don't worry lad, your English is good. Perfectly understandable. As to your question, the idea of a rating model for individual soccer players was something I hadn't previously thought of. So that's a great idea I'll definitely look into. In terms of power rankings, that's easy for me to create once I've done the ratings for players. Thanks for the comment lad, great ideas for future videos!

Or lad how can we get this model to suggest an over/unders game?

Hi lad, I’m really glad you enjoyed the video. The what-if analysis function is not really relevant in this video as the win percentages are already calculated for each team. When I use what-if analysis, it’s for a Monte Carlo simulation where I can simulate scenarios on a large scale. Then from there, the probability of each outcome makes itself apparent, e.g. Arsenal beats Man United 3450 out of every 10000 simulations, therefore Arsenal have a 34.50% chance of winning. I use this method when I do not already know the probability distribution for a game. I hope this helps lad.

Thank you for watching lad 🔥 I’ll try and release a video predicting this season’s playoffs using a basic model. If unfortunately I’m unable to release this due to it being too late to publish, I’ll definitely release a video walking my subscribers through how to predict NHL games/seasons/playoffs using Excel in the short term 💪

Hi lad, thanks for the comment 🔥 As of right now, unfortunately I don't know a reliable website where you can find ELO ratings for leagues outside of Europe 😢 I'll be sure to post it in the community tab of the channel when I find one of similar quality to the ClubElo ratings 👍

G'day lad, sorry for the late reply. First of all, make sure your Game_ID formula at 3:21 of the video is correct. Then, at stage 8:15, go into Name Manager in the Formulas tab, and make sure you exactly type in the same formula I do under the name 'games'. If you still have further troubles, try to be as specific as you can when commenting so I can help you fix it lad!

Hi lad, if you already have the decimal values then it just involves changing the format from General to Percentage in the Home tab. However, if you're unable to get any values, double check your XLOOKUP formula; if you don't have access to XLOOKUP use a VLOOKUP formula 🔥Thanks for watching lad!

Thanks for watching lad, but unfortunately the website I've imported the date from does not provide first half / second half data for us to calculate. This is an extremely simple model, a more detailed football model which I'm planning to upload int he short term will go though half analysis.

You need to correct spelling error or check if XLOOKUP addon is in your excel version

=IF(AND(ISBLANK(C2)=FALSE, ISBLANK(D2)=FALSE),C2&" vs "&D2,"")

Hi lad, very sorry for the late response. This is the formula I have inputted for cell AT2 in the 'Raw Data' worksheet under the 'Game_ID' header: =IF(ISTEXT(C2)=TRUE,C2&" vs "&D2,"") Try this lad, and let me know how it goes 👍 .

G’day, thanks for watching! You can update the fixtures by deleting the old matches, and replacing them with a new copy and paste from the Club ELO Ratings Website (like we did at the start of the video). From there, use text to columns to clean up the data (again, exactly like the video). Then, click the Refresh All button to refresh all worksheets, and the model should update automatically 🔥

G’day lad, the website has data for all Major Leagues including the EPL, Serie A, Ligue 1, Bundesliga, La Liga and many more competitions. I would try the model on another day, such as before the next EPL competition weekend, to see those games come up. If this helped lad, make sure you subscribe for more 🔥 Follow me on Twitter @excel_ladz 💪

G’day lad, I’m using Excel with a Microsoft 365 subscription (the latest version). The ISTEXT function is available on all Excel versions dating back until 2000, so I assume your version can support it 🔥 Just make sure that your ranges are correct lad, and make note of the specific error your formula is returning (e.g. a #NAME? error) which could help you solve your problem 💪

Hi lad, in the near future I’m considering setting up a website/page where all of my Excel models will be available for purchase. Would you be interested in this lad?

@@excel_ladz Yes sir.

As long as the price is right and there is success, of course I buy.

Hi lad, I myself have found that the Poisson method is much more accurate than the data this model imports. The ELO data is based on scorelines, and gives no bearing to who was playing in matches and the importance of a match to each club... This means, a Poisson model, factoring player strength in each match, is a much more predicative method 👍 Very soon lad I'll be releasing a video on how to build this model on Excel 💪 Thank you for watching lad!

@@excel_ladz yes agreed - it would be good if you could alter the future model to exploit the other markets which aren’t as liquid and are more exploitable, like win to nil, for example. The 1x2 markets are notoriously tough to beat as the bookies have these models dialled in. I personally have been having more success with bet builders and goalscorer markets. The goalscorer market you can reverse engineer the market odds to give some success.

Thanks for watching lad 🔥. To update the mode for the upcoming games, simply copy and paste in the new raw stats from the website (the text to columns part only takes a couple of seconds). Then, go into the Data tab and hit Refresh All or Refresh Data. If you do these two very quick steps (takes under 30 seconds), then the model will update for that new day 💪 If this helped lad, make sure you’re subscribed 👍

@@excel_ladz oh I am subscribed lol 😂

@@excel_ladzm having issues.. I can’t workout a few things. For some reason I keep getting 0 for team names on the dashboard. And I have 0% on the heat map. I’m now at a point where I’m stuck. Can you just add a link under the videos to where we can just download the sheets? Cheers, and I must say; this is the first ever RU-vidr I’ve seen where you don’t need to even ask for subscribers. When I try to save the worksheet, my laptop says some data maybe lost if I save as a CSV file. What shall I do? When I updated the sheet, it seems to only put data in the left hand side of the sheet in A1 only. And back to original format. I think it would be easier if you have a link to download the sheet from. Sorry if I’m being an absolute T@@T.

@@seychelles44 Don't apologise lad! It's my job to answer questions from loyal subscribers 👍 1. If you're getting a 0 for the Team Names in the Dashboard then there's a couple things that could be wrong. Firstly, check that you have access to the XLOOKUP function. Only recent versions of Excel have it. If you don't, change the function to VLOOKUP and adjust accordingly. Secondly, you may simply have just entered the XLOOKUP formula wrong. However, if the XLOOKUP formula (12:00) is correct, that means your Team ID's column has not been formatted like mine. This means the XLOOKUP is searching for the wrong range to return. 2. If you're getting 0% for the heat map, then it's more than likely it's the same issue here. Check if you have XLOOKUP, and then check if your data is formatted EXACTLY like mine in the video. 3. I'd save this file as a regular Excel worksheet. When you update the sheet, do what I do at 1:25 and go to the Text to Columns Wizard. I hope this all helps lad 💪When I become monetised on RU-vid, I'm looking to start a membership where I can build up this Excel LADZ community some more. Through this, the link to all Excel LADZ models will be available 👍 Thanks again for being a subscriber 🔥

@@excel_ladz nice one. I’ll check it out and see how it goes in a few days when I get more time. I’ll also look into other ways to find a system that works well with this. Cheers

That’s absolutely correct lad 🔥 I’ll make sure to update the video’s description in case some subscribers can’t work it out 👍

G’day lad, because this is a very simple model I haven’t included the process of a power query. If you’re able to, a power query would update the stats automatically for you. 👍

@@excel_ladz Got it right, now updating takes about a minute using power query....Thanks a lot Lad

@@bonezbooth1845can you send me yours sheet please 🙏

Most computer models use what's called the 'Monte Carlo Method' to calculate the probabilities. If you have any further questions lad, just ask me about it 👍

@@excel_ladz But not sure what model clubelo is using. Maybe Poisson distribution? But not sure how to get an expected mean solely based on Elo ratings.

Hi lad, the VLOOKUP function is slightly different to XLOOKUP so watch you don’t accidentally list the wrong ranges in the formula 👍 With a VLOOKUP in this video, it should be: =VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, FALSE) If you have a more specific issue with the formula (e.g. at one stage of the video) please let me know lad 🔥

@@excel_ladz The problem has been solved, but there is another problem I can't save the file with all the sheets. When I save the file, excel says that I have to save each sheet in the file separately. so I saved each sheet separately and when I open them again, the table with the formulas stops working actually all the formulas are missing. how the hell do I do that thanks for the advice.

@@excel_ladz having the same problem,can you type the formula using vlookup?

@@excel_ladz Hi Unfortunately I'm using excel 2019 and don' have Xlookup, can you provide a workaround for the lookup at 12:20 stage Thanks

Hi lad, make sure you subscribe because in the short term I'll set up a website/page in which these models can be purchased for a small fee.

Hi lad, I haven't yet combined ELO Ratings with the Poisson Distribution. With Poisson, I normally go a different route and calculate Attack and Defence ratings for each club and then for each matchup calculate xG. An example of this is in the first ever video I made on this channel.

Given two teams, A and B, with Elo ratings Elo_A and Elo_B, the probability of team A to win the match is: 1 / (1 + 10^ ((Elo_B - Elo_A)/400) ). For Team B, just swap Elo_A and Elo_B in the equation. Elo rating doesn't account for draws. Also I don't know how club Elo is calculating the goal difference using Elo ratings.

I am using poisson distribution to predict where I generate the xG and than use this in the formular

@@3ecke11er I didn't get your method, can you elaborate? You calculate the goal expectation probability of each team using Poisson, and then you multiply the two Poissons to get the probability of match results (0-0, 1-0, ...), right? At least this is the most common way of using Poisson.

@@-Tharos- This is correct

Hi lad, I just answered your question on the NFL Touchdowns video. Do you have any more specific questions?

I'll.look sorry lad cheers

Still no joy mate I've took a screenshot cab I send it to u

Hi lad, it should still work in Excel 2013. All you have to do is replace every XLOOKUP formula to a VLOOKUP. This is because unfortunately, XLOOKUP is only available in 365 Microsoft subscriptions as well as Excel 2021 and later.

G’day lad, I 100% agree with you. There are many flaws with this website’s calculations. Have a look at my NBA video where I walk through how to build a model, completely from scratch, on how to predict basketball games using player stats. Thanks for watching the vid lad, make sure you’re subscribe for more 🔥 Follow Excel LADZ on Twitter to be able to DM me about any sports modelling questions (@excel_ladz) 💪.

Definitely before the season starts 🔥

@@excel_ladz what league do you really recommend: NHL? Baseball, NBA, Soccer ⚽,? And which markets (MoneyLine, Spread, Totals)?

Hi lad, unfortunately this website doesn't provide stats for South American football leagues 😔 However, very soon I'll publish a video on 'How to Predict Football Games using the Poisson Distribution' that will be a much more accurate system of predicting matches than this video. Furthermore, you will be able to apply this to South American leagues 👍 Thank you for being a loyal subscriber lad 💪

Hi lad, not that I know of yet mate. This is a very simple model, make sure you're subscribed for more accurate models I'll be releasing very soon 💪.