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 💪
@@blackroyaltymgmt5195 Hi lad, as soon as I'm monetised on RUclips I want to create a channel membership. This will be a community of lads, where everyone will get access to all Excel LADZ files and Exclusive Videos. There's been incredible interest lately, so if you want to ask me more questions about this, ask here, or on Twitter @excel_ladz 🔥
New subscriber here. Great content. Question: I entered the long formula from 3:16 and it worked, but when i drag it down only half of the players on the team show along with several " " errors. Any ideas why? Thanks again for this super helpful video!
while its a good video ASK YOURSELF WHY would anyone put something that works on youtube ? It would be a GREAT WAY to Lose any edge you might have. Think About It !
Thanks very much for the suggestion lad! That's an extremely popular request, so it's something that'll definitely be a video in the very near future... Make sure you're subscribed lad so that you can be notified of when it comes out!
Hello, very nice work. I work professionally in an analysis firm. I want to give you an advice. To predict the results of NBA games, you should only focus on the data of the players. You must find player strength using the average of the NBA players and their individual point averages. When reaching the average scores of individual players, you should calculate which data has the highest correlation and focus only on that data. For example; The assists statistic has a very high correlation with the average score, while shooting attempts have a very low correlation. If you are going to ask about the defensive part of the game, be sure, even we professionals cannot measure it exactly. :) I hope your channel grows because you deserve it. Good luck.
Hi, I tried and did a replica of this in my excel... I put 1,000,000 simulations but the progams didn't respond hahaha then the same with 100,000 so finally I puted the 1k that the video says... I want to ask you if I can use this document as a start to make what you said? or it is necesary to make a new document from 0? Thanks in advance!
@@ScoutCheff Hi scott, may i ask your opinion of a logistic regression model for predicting the winning team as compared to the model shown above? To me, I understand the intuition behind predicting the individual scores and adding them up for comparison, but I feel like a simple comparison of net scores of each team should do the job as well. Thanks in advance!
Awesome video! One thing I am stuck on when trying to replicate this is that your player stats seem to only have player x on player y team. The data I pulled for this season is formatted so that if a player plays for multiple teams in a season there are multiple rows for each player (i.e. Mo Bamba has a row for TOT(total), a row for ORL, and a row for LAL). Was wondering if you had to deal with that for this video and if so how you manged to get it correct so that when data is refreshed it just shows the current team. Thanks!
Love the I'm having a problem with the bench players rankings I keep getting 1 I followed everything you did and everything you recommended in the comments and I'm still getting the same result hopefully you can help
Hi lads! Unfortunately, there was an error when I typed in the formula under the 'Player' header in the 'Player Working' worksheet. The formula is all right, until the last ROWS part of the formula. This should actually look like: 'Raw Player Stats'$E$3:E3))) Rather than: 'Raw Player Stats'$E$3:E4))) That is why the first player in every team is missing, because in the video I mistakenly put E4 instead of E3. So sorry for the confusion lads, Let me know if this fixes this problem 💪
Thank you for this, when I put in this formula, it returns the same name in all cells. I’ve checked several times to ensure the formula is exact and it is. Any idea why this would happen? Edit: Nvm, I figured it out. On the final “rows” section, instead of “$E$3:E4,” I had “$E$3:$E$4” making the end absolute, so it would only return the first name on the list.
And one final observation. Again, all from a constructive criticism from a handicapper's (or punter's) standpoint. Key variables in NBA: back-to-back road games, back-to-back home games, discretionary reduced playing time (subject to trainer, player, and coach), specialty bench positions (such as defensive matchup, three-point specialist, etc,), and long road trips (4-to-6 game road trips, quite common in NBA), injuries, and probably a few others I can't think of. These are essential variables in the handicapper's arsenal that this model cannot compute. I really enjoyed building this model. Perhaps there is some value in it. But I warn serious sports bettors to treat this model with caution.
For sure lad, there’s always more data out there 💪 I know matchups are significant, so that’s probably something I’ll add to the model in the future. Thanks for watching, sign up to the Patreon to get updates 🔥
So I ran model for tonight's (1/30/24) matchup IND @ BOS. Right now Vegas books have 244.5 (Over/Under). This model has 215 total points. These are the two highest scoring teams in the NBA per team scoring average. Again, not trying to mock or ridicule. This is all constructive criticism from the standpoint of a serious sports bettor. But if total points is that drastically skewed, one must assume the model is drastically skewed as well. Please tell me I'm misunderstanding something here. All the best.
Hi lad, this is the video where I made the BINOM.INV change (as explained in a response to a comment you wrote earlier): m.ruclips.net/video/yK0LmgngyuQ/видео.html Thanks a lot of watching and commenting lad 🙏 I appreciate the support 🔥
@@excel_ladz I changed BINOM.INV. Ran simulation for tonight LAL @ BOS game. Returned Total points 221. Oddsmakers have it at 242. I really do like your model. I think it's excellent. But unfortunately it still skews heavily UNDER on point totals.
I made corrections you suggested. Model still skews heavily UNDER on point totals. Maybe if you rewrite to skew heavily OVER on point totals, you could find the middle ground. Just a thought. Best
I did the players function exactly as you showed and it will not show the top players for each team ex. Tatum, Giannis Davis will not show up? any suggestions?
Hi mate! Apologies, this was a typo in the model while I was making the video. I've explained it in a comment, which I'll copy and paste here: Hi lads! Unfortunately, there was an error when I typed in the formula under the 'Player' header in the 'Player Working' worksheet. The formula is all right, until the last ROWS part of the formula. This should actually look like: 'Raw Player Stats'$E$3:E3))) Rather than: 'Raw Player Stats'$E$3:E4))) That is why the first player in every team is missing, because in the video I mistakenly put E4 instead of E3. So sorry for the confusion lads, Let me know if this fixes this problem 💪
@@excel_ladz thank you appreciate it. I also have one more question don’t know if you would be able to answer but I’m using excel on a Mac and it won’t allow me to use html link for basketball reference hence it will not update daily any suggestions ?
@@almanon6151 No worries bro. I actually built the model using Excel on a Mac, so there's definitely a solution 👍 Have a look at the 'Currency Converter' video on my channel, where I run through how to establish a Power Query on a Mac. Instead of using the currency website, I used Basketball Reference 😃
Hey man, first off amazing video! I've learned more in this one video than I ever did in my spreadsheet class lol. @ 36:40, I am completely stumped by the formula to simulate the points. Excel keeps saying I have too many arguments, do you think you can help me out?
Hi lad, I’m glad you enjoyed 🔥 If you’re getting the ‘too many arguments’ error, there might be a comma that is missing somewhere (or even one too many commas). It may be very bland advice, but just scan through the formula in the video and write it out slowly. You’re getting an error message, not an error as a result - which is encouraging. This means that you’ve probably just have a minor typo in your formula rather than an error in your model 💪
Hey buddy. Seeing an issue with NBA simulator. If a player has a 0% statline, let’s say on 3 point shots, no matter how many times you refresh, his projected score won’t change… a good example would be the Hornets, and Mark Williams… he doesn’t shoot 3s, so his pts sim stays at 5
Hi lad, thanks for watching 🔥 The BINOM.INV function can’t compute when there’s a 0% stat line; it just amounts to an error. So what I’ve done in wrap it in an IFERROR function. If there’s a 0% stat line which computes to an error, I’ve made the projected points return a player’s average points scored throughout the season, adjusted to the amount of minutes he’s expected to play in the game. As a result, this will always stay constant.
@@excel_ladz ahhhh ok... Thanks for the explanation. Makes perfect sense. I was wondering why a couple players at the Center position (0% on 3s) would never change.
Thanks for the support lad, I'll publish the Injuries video most likely within the next 2 weeks. It's a fairly manual process and involves checking ESPN for player injuries/rests and factoring it into the Player Minutes tab. Then, you may increase another player's minutes if they're expected to play a bigger role as a result of an injury/rest.
I really appreciate your video. I have learned more from you then any other person giving instructional videos on this topic.... My question is, In the simulated points column, on results sheet, some players points never change after sim is completed... can anyone help with this issue?
Hi lad, thanks for watching 🔥 You’ll notice at the end of the function simulating a player’s points in the match, I added in a condition with a ROUND function. Basically, the BINOM.INV function can’t handle values of 0, so of a player has a three point percentage of 0 so far, the BINOM.INV function returns an error. This results in the whole model returning an error, because you can’t include an error on the SUM function for computing a teams total points. The ROUND function essentially says that if there is an error, then return a players average points so far this season, adjusted to the minutes they are expected to play in this match. This value is constant, and as a result doesn’t change with each simulation. I hope that helps lad 👍 Things like this usually arise in the error checking portion of the model building process - very tedious stuff indeed 😂
Loved it, loved it, loved it, By any chance do you have anymore for the following: NFL, MLB, College Basket Ball, College football. Thank you for all that you are doing.
Thanks lad, appreciate it 💪. I'll be releasing a flurry of models in the short term, especially a beginner MLB one before the season starts... Make sure you're subscribed to be notified of these videos lad 👍.
Thank you for replying, I am noticing with the NBA Probabilities that some of the teams that I switch to in results I get the "#Ref! in the cells . Then I don't get the predicted percentage or score data ... Why is this happening ?
G’day lad, so sorry for the late response. For some reason RUclips doesn’t give me notifications for responses to existing comments which is annoying. As to your question, this is happening because of mid-season trades and how the imported data has adjusted accordingly. There is a simple, 5 minute fix that I’ll be posting within the next couple days to rectify this issue properly lad 🔥
Hi lad, the DEF Rating is applied right at the very end of the video to scale a team’s shooting attempts based on the defensive strength of their opposition 😃
Hi, thank you so much for doing this, it looks like a great model. Can you explain how you created the line fit plot graph which gave you the numbers for xFGA+FTA? What inputs were used? Thanks!
G'day lad, I found this formula using linear regression. I took the relationship between every player's MP and FGA + FTA (using the 2022-23 season's data) and using linear regression, found the trend line; which is the quadratic formula in the cells "xFGA+FTA" you're asking about. At the 30:25 mark, the graph portraying this relationship is at the top right hand corner of the screen. I recommend having a look at the ‘Predict Football Matches with Possession’ video on my channel, where I go through this linear regression process 👍
@@excel_ladz Thanks! I'm playing around with a way to predict players' individual stats in a game using the same simulation method to find the most likely outcome based on averages and defense. This is very helpful!
@@tristanmortensen173good morning... Can you kindly share when you are done? And also I have issues getting the 'long formula ' can you also help with that?
Does anyone know why Giannis is listed in the raw player data table, but when I do the player column on the “Player Working” sheet he doesn’t show up? Everyone else but him does.
Hi lad! Unfortunately, there was an error when I typed in the formula under the 'Player' header in the 'Player Working' worksheet. The formula is all right, until the last ROWS part of the formula. This should actually look like: 'Raw Player Stats'$E$3:E3))) Rather than: 'Raw Player Stats'$E$3:E4))) That is why the first player in every team is missing, because in the video I mistakenly put E4 instead of E3. So sorry for the confusion lad, Let me know if this fixes this problem 💪
@@excel_ladzThat’s how I have it but, when I drag it to add the rest of the players, it skips the player at E4 (which I presume is Giannis) and jumps right to Beasley at E5. After that the rest of the players are there.
Sir: Finished your video.. .all inputs are in but can't do simulations when hitting f9..only one number changed in one column,, can I send file to you? thanks bob
I also just simulated a game between ORL @ LAC and final total is 181. But when I sum Points Sim LAC scores 120 points, ORL 115. Round function seems to be rounding way down.
Thanks for the video. I’m running into a snag when trying to average the def rating. It’s giving me #div/0. The numbers are showing up correctly for the corresponding teams. I think it’s reading the cells as empty. I’m not sure why
Nice video, but I have problem with Player working part. I tried to put this same formula to 2023/2024 nba player stats and now some of these players repeat multiple times on Player Working tabel. Do you know how to fix it?
Hi lad, this is definitely a problem. The Basketball Reference power query repeats the player’s stats for each team they play in, so names can appear more than once. I’ve solved this issue on Patreon for my members, but I reckon I’ll put up a short explainer video for all the free members on how to fix it. Basically, it just involves using the UNIQUE function in grabbing all unique players (so they don’t repeat), then matching them to their current team (while still taking into account their stats from their whole season). This then makes sure that there’s no double ups lad 👍
Hi lad, thanks for watching 🙏. I imported the data to Excel using a power query. If you’re unsure on how to do this, check out my Power Query video I uploaded a couple months ago (the process is slightly different for Windows and Max though). As to your question, I think I’ll upload a specific video in importing the basketball stats as a few subscribers have been having problems. Make sure you’re subscribed lad 👍
Hello, after watching this video, I am wondering if this model can be turned into a regression model that can better predict the outcomes of each game. I am new to all of this stuff so I apologize if I am wrong and this can’t happen.
Hi lad, it could be 😃 If you could devise a regression analysis with significant predictors then the model could come to an expected points figure for each team that is more accurate 👍 To then find the Win % for each team you’ll have to simulate the Expected Scorelines along an appropriate probability distribution.
Oi for scoring when u do sequence 500, what’s the difference if I were to sequence 500 the individual scoring on all the players first on the TeamResults sheet and then sum those up to get a total score.
Hi lad, if it works it works 💪 There’s plenty of different ways to come to a simulated scoreline using this model. As long as you keep the fundamental reasoning behind the model clear - that the total score for a team is the sum of its players simulated scores - then the model will work just fine 👍
G’day lad, that’s my mistake. I’ve written a comment on how to fix this 👍 Scroll through the comments, and let me know if you don’t manage to find it 🔥
Another question. If I want to bet individual player point totals, the model returns results with extreme variance. For example, tonight's game PHI @ POR, the model has Embid scoring 39 points. After refresh, 34 points. After another refresh, 26 points. So what number can I trust? Or would that require an entire different model to establish total points for individual player scores?
In terms of the player’s points, it’s a simulated point total. To get the probability of a player scoring under/over a certain threshold, you would need to simulate a player’s points in the model 1,000 times using a data table. This is exactly the process I did with the sum of a team’s points in the video, except you can just do it with a player’s points in this case. Then you would use a COUNTIF or SUMPRODUCT function to calculate how many trials fell under/over your threshold - dividing by the number of trials is your relevant probability.
Sorry, but one more question. I looked at all the games tonight, Monday, January, 29. The Model makes every game go Under the Vegas total by large deficits. What could that be a function of? As an example, IND and MIL both average 124.4 points per game. I plugged these teams into model, and I get a final of 105-108, for a total of 213 points. Just based on team scoring averages alone, that's off by 35 points.
Hi lad, thanks for watching 🔥 This issue was to do with the non-simulation of a player’s shots taken. This underestimated the points scored like you’ve seen in your trials. To fix this, I wrapped the BINOM.INV function for each shot type in another BINOM.INV to simulate the xFGA+FTA. This gives much more accurate over/under info. I’ll answer all your other questions under the relevant comment 👍 Most of the things you bring up have in fact been patched for a more accurate model 😃 These updates are available on the Excel LADZ Patreon (link in description).
Hello!! Huge fan of the video, extremely clear and helpful. Unfortunately, I ran into an issue I believe after my sorting function. On my players working tab, the home & away team on right hand side are both in order of players based off minutes played. But on the results tab, some teams have all 5 starters correct. But a few like the suns, have Diope and Goodwin starting but having Eric Gordon and Grayson Allen on the bench.... Or Oubre Jr starting on 76ers.. Don't quite understand why some teams have right starters but others don't. Haven't proceeded until i can figure this out. Please any insight would be super helpful. Thank you so much in advance. On top of what you have already done.
Hi lad, thanks for watching 🔥 Sometimes a player’s MP can be the same as another player on the same team. This can cause the ranks to muck up. You can fix this by adding a small, insignificant decimal to each player’s MP using the ROW function, or you can just change the MP’s in the Player Working worksheet manually so there are no repeats. There’s also some instances of players appearing for their former teams due to the formatting of the Basketball Reference dataset. If this issue is occurring lad, you should make a new column of players in the Raw Player Data worksheet using the UNIQUE function or an equivalent formula. Then you would link this column to the rest of the model to make it work. I hope these suggestions can offer some value lad. If not, then every issue/bug can be resolved by joining the Excel LADZ Patreon where you can download the model 🤠
I found two problems. First, Lakers Player Taureen Prince and D'Angelo Russel both average 30.5 minutes per game. Both play PG. So the system puts D'Angelo Russel in the lineup twice. Starting and Bench 1. Second, Basketball Reference has a position acronym PG-SG, SG-SF in the position box. That causes XLOOKUP to fail. Any thoughts on solving these two problems? I manually overwrote 30.7 minutes in Raw Player Stats for Taureen Prince and changed SG-PF to PF, and the model puts him back in the lineup. I'm assuming we'll need to make these adjustments on a case-by-case basis manually? Anyway, great model. Thanks for sharing. I learned a lot about Excel writing it.
Hello, this model is great. I am seeing some of the top players not show up on their respective teams. Example is Giannis, Anthony Davis, and Luca. Any thoughts on that?
Hi lad, I just ran regression using the MP as the independent x variable, and FGA+FTA as the dependent y variable. That produced the graph shown at 30:10 👍
Thanks for watching lad. If nothing is showing up, first of all make sure you are absolute referencing (according to the formulas I've done in Player Working). Also, especially if you've wrapped it in an IFERROR function to equal "", if there's an error, there's probably a minuscule error in there somewhere that just needs a slight fix (like absolute referencing). Just rewind the video back, and if you need me to answer any more questions I will.
I just want to say you are a fking lad for these videos ! I am doing a university assignment for Data Analytics and we have use Monte Carlo, whatif analysis (E.g. goal seek, solver), breakeven analysis, hypothesis testing or regression and other statistical topics such as median/mean to tell a story about something. I want to use monte carlo to predict the probability of a team winning when their best team is performing below. HAVE YOU GOT ANY ADVICE FOR ME or which videos of yours's I can watch that will help me ? We have not learnt Poisson Distribution or anything mentioned outside of the above topics. How Would you tell a story about how we can use data analytics to predict outcomes on sportsbet ?
Any idea what I am doing wrong at 26:54, I have put the same function as you but it returns 1 for each benched player. (I have different page names than you) =MIN(IF(ISNA(MATCH('Player Working'!$H$5:OFFSET('Player Working'!$H$5,'Player Working'!$C$5-1,0),$M$12:M18,0)),'Player Working'!$H$5:OFFSET('Player Working'!$H$5,'Player Working'!$C$5-1,1))) Thanks in advanced love the video so far!
Hi lad, thanks for watching the video! Apologies for the late reply, I get upset with RUclips because any comments with a formula is presumed to be spam that I don't get notifications for... Here is the correct formula for cell M18 of worksheet 'Results': MIN(IF(ISNA(MATCH('Player Working'!$H$4:OFFSET('Player Working'!$H$4,'Player Working'!$C$4-1,0),Result!M12:M16,0)),'Player Working'!$H$4:OFFSET('Player Working'!$H$4,'Player Working'!$C$4-1,1))) If you drag that down for all the benched players lad, it should work. Let me know hot it goes 👍
Hi, im also having problems with his I tried both and for the first one I get back 1 for cell M18 and the second one give me a 0 any idea how to fix this? Thank you in advance 8 @excel_ladz a
@@excel_ladz Thanks for putting this video out, this was one of the best NBA simulation model i've seen so far but the formal for benched players still returns 1, i use google sheet, please any idea on how to correct this on google sheet
Hey, quick question, rather 2 part one. 1. When sorting the player ranks in the player working sheet, the value is descending rather than ascending. Could this be due to the fact I'm using Google Sheets? 2. When entering the players in the results tab, it's not the correct starters showing up and getting an Error message when trying to enter the bench players. Cheers Mate
Hiya, currently creating this model to get experience in excel. Having one slight problem that I can't seem to solve. When sorting the players on the "Player Working" sheet all of my players are sorted from least time played to most time played. This is causing me more issues when I work on the results page. Any help or advice on how to get them sorted from greatest to least would be much appreciated. Amazing work and great tutorial. Keep up the good work!
Amazing video. Can’t tell you how much I respect and appreciate the effort you put into this! Question: I recreated the database, but the simulation page encountered a problem when players averaged the same amount of minutes. For example, Isaiah Stewart and Killian Hayes of the Detroit Pistons both averaged the same minutes per game, when it loads their rank it brings Stewart over twice and Hayes zero times. What is the solution to this problem?
Thanks for watching lad 🔥 I recommend manually changing one of the player's MP in the "Player Working" spreadsheet. For example, you know Stewart and Hayes have the exact same MP (let's say 23.2). If you want Stewart to be prioritised in the starting lineup, then you would write over his MP formula in Column B of the "Player Working" spreadsheet to 23.3, while keeping Hayes' MP exactly the same at 23.2. The formula sorting the MP in Column J would then recognise this, and so your "Simulation" page shouldn't have any issues from there.
Great video, I'm doing the part where you sort the MP with the number of players but when I sort it, it puts the highest minutes at the bottom. Do you know how to fix that?
I have some questions, the first how would you go about taking defenders defensive rating against the offensive players individually, so we can calculate how how that effects the players and therefore the teams total points. is that worth a vid or is that simple enough, if i did this video easily. Secondly how would that relate to the teams offense rating % for how that affects the individuals and the teams overall score, does that individual defensive rating equal the teams defensive rating adjusted?
Absolutely, a more advanced model would explore the fact that players have matchups in a game. This would also be a major factor in simulating rebounds. However, it’s challenging to come up with individual defence ratings that are reliable. Definitely worth a video though.
Please how do I use the power query on excel, I am mostly interested in the basketball total points prediction. Could you maybe show me how to be getting the stats to excel daily and process mainly for the over or under prediction. Thanks
Hi lad, a power query is a method of importing data from a website into Excel. With a power query, the data is updated on Excel automatically as it is changed on the website, meaning you get live data daily and don’t have to manually change the stats when using the model. That’s how I got the data on my Raw Player Stats spreadsheet at the start of the video (the link to the website I got the data from is in the video description) For my videos, I use a Mac. This means the process of doing a power query is different from a Windows computer. In order to help fix your problem lad, can I ask what computer you are building the model from? In terms of the over/under lad, that will be a separate video uploaded within the next 2 weeks that I’m really looking forward to showing everyone.
@@excel_ladz Okay, thanks for the explanation. Please kindly do the video for just the over and under points prediction. My system is windows 10, if you could just show the process for the power query on that points video, that would be great. 👍🏻
Any idea on how to speed up the data table process? For both my Mac and windows computer it takes about 10-15 minutes to simulate all 1,000 games. I also tried to update the data table using the update sheet feature just to see different projections and all the 1000 projected games became the same value
Hey, can you make a table like this but for football, for example, the English Premier League? But that the players are chosen manually. I would modify this one for the NBA but I am not sure what statistical data to take for each player and which formulas to use.
Hi lad, the data in this video is imported into Excel using a power query. This means that as the stats change every week, they are automatically changed in my Excel spreadsheet without having me going back and changing anything. If you found this video helpful lad, make sure you subscribe!
Hope you see this, appreciate the video, but I got a question. When I lookup the players on each team, it doesnt show me players that have been traded to the team during the season. It keeps players on the team they originally were on and shows the TOT stat line instead of the stats for their time with either team
Hi lad, thanks for watching 🔥 You’re absolutely right. After making the video, the dataset from Basketball Reference changed so that it repeated players who have been traded. Following this video will fix your problem lad: m.ruclips.net/video/cJOZyahy-Yo/видео.html&pp=ygUOZXhjZWwgbGFkeiBuYmE%3D There are also other NBA Videos on the channel that have improved the model 👍
Hi lad, the Power Query that connects Basketball Reference live data to the model makes sure that each team’s roster is completely up to date. However, if there are injuries on a team you’ll have to manually change a player’s MP to 0 in the ‘Player Working’ worksheet.
Do you have any suggestions on how to add projected assists and rebounds for each starter as well? I added an extra tab running 1000 simulations for each starter and 2 bench players to give a more accurate points total projection for the players and would like to try something similar for rebs ands assists.
Could You tell me ( and please avoid the obvious answer of more is better ) what is the minimal sample size where you could say ok I think this system is good ? 300 plays ? 400 plays ? 500 plays ? Also I am guessing it should be about 400 or 500 ? since win/lose is binary . Last But Not least I am guessing a win % of 56% or better should be profitable ?
G’day lad, thanks for watching the video 🔥 In terms of the sample size, its whole point is to be an indicator of the simulations. So for this model, 2,000 simulations was good because the Win % would only ever fluctuate by a maximum of 1-2%. Again, if you only did 500 simulations and the model fluctuates ver little, that would be an acceptable sample size. I hope that helps lad. I’ve set up a Twitter account where my handle is @excel_ladz, so if interested you can follow and DM me so that I can help you more personally with building sports models 🔥
Hello. Thanks for the good video. I have a question. Not all players of the team are displayed on the "player working" sheet. For example, Phoenix has no player Deandre Ayton. Although it is on the "Raw Player Stat" sheet. The formulas are the same as you have in the video. I'm updating the sheets.
I will add: on the "Player Working" sheet in the Player column, formulas are found not from the first player of the team, but from the second player. It turns out that on the "Raw Player Stats" sheet there are 18 players of the "TOR" team in the table, and the formula on the "Player Working" sheet finds only 17. How can this be fixed?
@@nighttime7849 Hi lad. It seems like the problem may be that your "Player Working" sheet is excluding the first player on every team. Because the formula I drag down at 13:20 grabs the players alphabetically, this may be the reason why only Deandre Ayton is excluded from the Suns team... To fix this, I would try two things. Firstly, make sure that the ranges in this formula at 13:20 are appropriately absolute referenced; according to what I have done in my video. Secondly, I would make sure not to forget the +1 in the formula at stage 12:50 of the video. Try reviewing these things, and let me know if you've made any progress lad.
@@excel_ladz All formulas are the same as you have in the video. There is still one player missing from each team in the table. Using the formula, I checked the number of players of one of the teams in the "Raw Player Stats" table. Always -1 player
@@excel_ladz I have the exact same problem, if you check your video at 15:02 you will see that the result for the Miami Heat is 15 players, but actually they are 16 in the Raw Player Stats. The formula does't get the first player from the team, in this case Bam Adebayo.
@@nighttime7849 i don't know if you managed to find the fix, but at the end of the formula it need to be: ,ROWS('Raw Player Stats'!$E$3:E3))),"") ; Hope that helps.
Hi Bob, if you are referring to the simulations in the 'Simulations' worksheet, there may be a couple of reasons as to why it isn't working. Firstly, make sure that that the range you're highlighting for the data table is the range A2:C1002. Secondly, 1000 simulations of the scoreline may take a lot of time for your computer (depending how old it is). Give your computer time to process, and maybe try clicking on the 'Refresh All' icon in the Data tab if it isn't working. If these steps don't work Bob, you may have just entered a slightly incorrect formula. Keep trying though and send me all your questions because, unfortunately, I'm unable to receive and fix Excel files for subscribers.
Thank you for sharing this is great. So Im stuck at the part where you copy and paste to start creating the away team in player working tab. The left side of players MP, NO of players an positions show up but the right side only a few names pop up and the rest of the cells are blank or say N/A. i cant seem to get it fixed. any suggestions will be greatly appreciated
Hi lad, thanks for watching 🔥 I'm looking to upload a complete NFL model soon, but I haven't made a college basketball model yet. Great idea though lad, I think there would be a lot of interest 💪
Hey awesome video. With the new nba season starting this week should i just wait for games to be played? Or can I make this model with old stats anyways and just swap it with new stats when enough games have been played?
@@excel_ladz Hello again, what should I do if 26:25 this formula comes up as 1 or 0 for the subs? I tried changing up a few things but cant seem to figure it out
Hello ladz, i dont have the what if function in google sheets is there a way to simulate that in google sheets or another way to run the 1000 sims? Or do i need to rebuild in excel(which i dont have)?
G’day lad, there is an update video I’m posting very soon that will instantly fix these issues. If you have more questions, comment again or follow me on Twitter 🔥. My Twitter handle is excel_ladz 👍
What's up man! I love all your videos. You are an excel wizard my friend! I was playing around with this concept for the WNBA and I wanted to get your thoughts. The biggest hurdle is that on basketball-reference they don't track the adjusted team metrics and also they don't label some positions the same as their NBA counterparts. For example there is not PG or SG it's just G or G-F or F-C in the WNBA stats. Regardless, I made a sheet using this setup and it seems to be working. I just wanted to see if you had any thoughts about this kind of project
Hi lad, thanks for watching 🔥 In regards to the adjusted defensive ratings, you could compute those yourself. It would involve compiling a team’s defensive rating, and then scaling it to the quality of the opposition they have played. A really easy method would be to multiply the reciprocal of the average attack rating ( so 1 / ATT Rating) a team has played all season by their own DEF Rating. For example, a team with a DEF Rating of 0.96 would be scaled down (or better) if the average quality of their opposition was above 1 (e.g. 1.02). The positions in the model don’t matter too much, as you can always manually change a player’s expected minutes in the ‘Results’ worksheet. 👍 Btw, I just checked out your channel and it’s great lad 💪 I’ve subscribed and I reckon you’ll hit 1,000 pretty soon 🔥
@@excel_ladz I had this problem too, except it's only returning the first player from each position, but the team is starting two forwards. How do I return two unique players at the same position into the results sheet?
I need a video on how to work with a power query. I’m trying to get the data from Basketball Reference, but I can’t figure out how to transfer it to the excel spreadsheet.
It's hard for me to help you greatly without knowing the brand of your computer. For example, I have a Mac, and the process of importing data using a power query is slightly different than it is on a Windows computer. If you have a Mac computer lad, you should go check out the Power Query Video I have on my channel 👍
@@ScopophobiaTapes That's perfect lad, you can do a Power Query. I suggest you watch my video titled 'Excel Power Query for Mac Tutorial | Currency Converter Example'. This video will take you exactly through how to make a power query from start to finish; using an example of importing live currency data. All you have to do is change the link you're importing from: switch to Basketball Reference (in the description of this video) instead of the currency data. Let me know how it goes lad 👍
Great content. I've tried similar for an F1 race using lap times. Only one question regarding the totals. When I try and simulate them I get the same number all the way through. It works fine when using rand() but with the sum() function it repeats the same value. Any thoughts why that happens would be appreciated
G’day lad, thanks for the support 🔥. It’s very unusual your SUM formula isn’t working. If it just stays stagnant, then there may be the possibility that the Excel Workbook is buffering and is too slow to update… Other than that, I would try to tweak the range of the formula to see if that works. If this helped lad, make sure you’re subscribed for more 💪
Hello, Can you really post "HOW TO DO THE POWER QUERY " to retrieve the data from the web. I have tried effortlessly to get this right and I can't. Because the way the tables are setup copied over , if it's in a table and when you try to get the average =AVERAGE(C2:C31) of all of the teams you get the #DIV/0 error no matter what. PLEASE HELP....
my excel seems as if I use a formula, I cant use the numbers I got in the formula to use in the next formula. for example I transfered the Basketball ref defencive ratings using a formula. but then couldnt use that data for the average. it was showing #DIV/0! error
Thank you for tutorial, I don’t have any previous experience working with excel or sheets, but it was easy to follow your instructions. I have a question especially after trades happened. Players are still on roster even they been traded to other team because of stats data so if player traded he’s on 2 teams which effects team points simulation. How to react on that situation? Thank you.
G'day lad, it's very good to know relative beginners are able to follow my videos. As to your question, the data in your 'Raw Player Stats' worksheet should be imported using a power query, which grabs live data from the internet. Therefore, if a player is traded, their team will update automatically and thus you want be able to select them as part of their old team in the Team Points Simulation anymore. If you found this helpful lad, please subscribe!
@@excel_ladz Whats the difference if I'm importing data whichs updates it real time anyway with =IMPORTHTML? Is it the same result as with power query, or it effects somehow result? Thank you answering stupid questions :)
@@kasparasjes8370 Hi lad, I don't use Google Sheets so I'm not entirely sure of IMPORTHTML's capabilities as a function. However, I can tell you that with a power query, any data that updates on its website will be updated on your Excel spreadsheet. All you have to do is reload the page. Have a look at my 'Excel Power Query for Mac Tutorial | Currency Converter Example'. This video goes through the process of setting up a power query on a MacBook, and grabbing live currency data 🔥
I'm having an issue loading all of the teams' players using the index function. I have to keep switching from Large, Small to get players. Is there another way I can go about getting all the players from the list? Thanks
Hey! I finally got to finish the excel file! such a jorney this afternoon! But now I have a question, how can you perform all simulations not for the expected lineup, but when you already know the lineups for the game. Im not sure if I mide myself clear.... Thank you very much
Hey lad, I’m glad you finished the file. Ideally within the next 2 weeks, I’ll post a video explaining how to adjust Player Minutes in the model. So as you said, if you already know how many minutes a player will play in a game (say it’s an hour before the game and the squads have been confirmed), then you can take that into account so it will affect the simulations accordingly. Basically, it involves manually changing the Player Minutes stats in the Player Working worksheet. I hope that helps lad.
Didn’t work for me the first time I put in an away and home team it works but any other time it says #N/A and for the Bskt. Ref . Def it said #N/A for half the teams😢 can someone please tell me how to fix it😢😢😢
I’m glad you enjoyed it lad, make sure you’re subscribed 👍. In the very short term I’m considering making a website on which all my models and courses will be available to purchase. Would that be something you’d be interested in lad?
When i go to sort the players by minutes played, it goes least minutes down to most minutes so it messed up the starters later on. i copied the sort function exactly. any ideas?
Sorry, LADZ, but the tied minutes glitch is really annoying. For my BRK predictive model it put the same player in lineup again. That's now twice on two occasions in one night. Easy fix is to manually tweak MPG AVG in raw data set. But this is annoying. I think you have a brilliant model, but skewed totals, tied minutes error, variance in Sim. Pts. (every time each player refreshes, wildly differing point totals), injuries, trades, resting games (i.e. reduced minutes on single or multiple games due to fatigue or soreness and completely discretionary for coach and player) create too many painstaking variables to make sheet practical for every day betting purposes. Now I may be completely wrong, but I'd like to see some serious data using this system against Las Vegas odds, Spread, Total (Over/Under), and Moneyline with at least a 1000 games. I can already tell Total (Over/Under) is going to be deeply skewed UNDER on every game. So that data will be useless. Moneyline and Spread would be interesting.
Hi lad, you can fix this issue by adding + ROW()/10000 at the end of every player’s MP in the player working worksheet (column B). This separates players by an insignificant decimal, so there can be no tie (and subsequently no error) in the model for MP. The PG - SG thing is annoying l, but could be solved by using the FIND function or some combinations of text functions that would return only the exposition on the left for that player.
Because you are importing the data with a power query, simply press the ‘Refresh All’ button in the Data tab of Excel and your database will update with new player and team stats correct to that day.
Hey lad, I've done everything as I should (I believe, I double-checked) but I ran into two issues: 1) when the player is averaging 0 attempts per game, for example, Ron Harper Jr. for Toronto Raptors this season, the simulation shows Div/0 error. 2) All point simulations are waaaay too low, oscillating between 50-70 points!
I "fixed" the first problem with the extra IF [expected attempts]=0 then 0, if not true then the formula for Poss. and the simulation, but the second issue of simulated scores being way too low still's there.
Hi lad, I’m glad you figured out how to patch the Ron Harper Jr situation. There might be an error with one part of your binomial simulations if you find the simulations to be too small. For example, the sum of free throws might not be calculating correctly. Without making any player changes, I put Lakers vs Bulls into the model for tomorrow’s game. The Lakers should have an approximately 56% chance with an expected score of 110. Bulls 44%, with expected score of 106. How far off the points sim totals for this match are you lad?
@@excel_ladz Thanks for reply, lad. I'm way off on the simulation for Lakers - Bulls tomorrow. I've got Lakers at 63 and Bulls at 57. Everyone's points seem to be off - James simulated for 9, Wood, Christie, Vanderbilt and Hayes all predicted for 0. On the Bulls side points are all low as well - i.e Vucevic predicted for 6... I'm not sure where's the issue. Maybe I've missed something but I double checked with your video and it looks to me like it's the exact same. The function written is: =IF($F27=0,0,IF(D27>0,IFERROR(2*BINOM.INV(BINOM.INV(F27,G27,RAND()),H27,RAND())+3*BINOM.INV(BINOM.INV(F27,I27,RAND()),J27,RAND())+BINOM.INV(BINOM.INV(F27,K27,RAND()),L27,RAND()),ROUND((F27/E27)*XLOOKUP(B27,players_data_raw!$B$3:$B$1017,players_data_raw!$AD$3:$AD$1017),0)),0))
I hope this is user error, but my feeling is ROUND FUNCTION or RAND FUNCTION is skewing data. But the total scores this model produces are skewed nearly 25-50 points in some cases. Here are last night's model forecasts against actual gametime scores: MODEL ACTUAL LAC 86 108 CLE 101 118 NOP 99 108 BOS 108 118 UTA 92 114 BRK 102 147 I would list them all, but obviously three is enough to demonstrate something is seriously off. Not trying to be critical. I love the model. But I bet real dollars of hard-earned money. I like to bet totals (OVER/UNDER). But something is seriously off in this predictive model. Given your demonstrated brilliance with Excel, I'm sure you'll find a solution. Or perhaps it's that old juggernaut STANDARD DEVIATION, which has been the eternal curse of all predictive models using probability. But clearly Pt. Sim. column and ROUND FUNCTION are skewed severely towards low-scoring totals.
hi Excel LADZ do the numbers in the formula cells "xFGA+FTA" have to be updated season after season or is it timeless? How did process to get them ? thanks you very much
G'day lad, I found this formula using linear regression. I took the relationship between every player's MP and FGA + FTA (using the 2022-23 season's data) and using linear regression, found the trend line; which is the quadratic formula in the cells "xFGA+FTA" you're asking about. At the 30:25 mark, the graph portraying this relationship is at the top right hand corner of the screen. This formula doesn't need to be updated season by season, as it stays relatively the same throughout time. Any change in the formula to reflect current trends would result in the smallest of changes to the "xFGA+FTA", which would be insignificant to the final result of the model.
Hi... I'm trying to recreate this, I'm at the minute 22 of the video... I have had to learn a lot, from how to export a table from internet to excel, to a lot of functions... and I'm from Chile, so I have a lot of problems with the data cause we use , to decimals, and all the excel does not understand very well . as decimals... but I have tried some solutions and have worked... but I have a question, cause there is players with more than 1 team, and to the "minutes per game" it takes the higher number... I tried to solve but I coudn't... I'm a home user, not a pro so I tried to put some IF but I couldn't solve it... so can you help me to resolve that? what I have to add to the statistics is not the higher, but the respective to the team! Thanks in advance and for the video!
Hi lad, I’m very glad that you’ve raised this issue. I uploaded the video before the NBA trade period, and so you wouldn’t of had any issues with this step then. However, when players changed teams during the trade period, the dataset created a new column instead of just changing the team value… To fix this problem for subscribers I’ll be uploading a very simple solution video on the next couple days. Make sure you’re subscribed to be notified immediately of this 👍
@@excel_ladz As I told u, I'm making this... and I have a problem... today pacers are playing against the rockets... and in the rockets kevin porter has the same MP as jalen green (35.5).. so in the rank colums in the results sheet, both of them appeared with 1... I investigate and add this to the function: +COUNT.IF('Player Working'!$J$5:J5;XLOOKUP($B13;'Player Working'!$I$5:$I$30;'Player Working'!$J$5:$J$30))-1... My players in result started in B13, you in B12... tell me if thats ok to dont repeat any rank please?... and other problem that I haven't found a solution is josh christopher is from houston rockets, but my "player working" sheet doesn't recognize him as a rocket's player... I don't understand why and I don't know if this repeat for other player in other team... I don't know what to do. That my inform for today hahaha Greetings!!!
@@tenienteale G'day lad, I assume after watching my updated video that your problem regarding Josh Christopher being on the wrong team is solved. However, there is still your Rank issue which I'll address below. Due to the way this model is designed, it's not okay to have two (or more) players with the same Rank. The model will make a mistake in making total minutes played more than 240, which is unfair for the other team. Below is the solution: In the MP column (Column B) of the Player Working sheet, you can manually change a Player's Minutes Played so that two players don't have the same number. Just write over the formula with the Player's Minutes + 0.1 (so there's no material difference). For example, if Irving and Doncic both had 33.4 MP, then you can leave Irving. For Doncic's MP, write over the top 33.5, and then the ranks will be updated accordingly. Let me know how this goes lad 👍
do you factor somehow for the opponent or do you sample a team's points solely based on it's players' stats regardless of the opponent? I don't think you do, but perhaps I missed sth important.
Right at the end of this video there is a section called "Defensive Adjustment". This takes into account a team's defensive strength, and adjusts their opponent's scoring opportunities based off of that. For example, a defensive rating of 1.05 (which the Spurs had), is bad. This defensive rating is multiplied by their opposition's xFGA+FTA, so that the other team has more opportunities to score because of the Spurs' poor defence.
Hi lad, thanks for watching 💪 You need to import the data into Excel using a Power Query. The links to the Basketball Reference websites are in the description of this video 👍
Thanks for the support lad. Unfortunately, I don't share my Excel files with subscribers. Instead, I prefer to give a full tutorial (from scratch) on how to build it. If you have any specific questions or problems you might run into building the model on Excel, comment on the video and I'll get back to you as soon as possible lad.
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 💪
Can we download this file that you made? An make updates to it ourselves??
@@blackroyaltymgmt5195 Hi lad, as soon as I'm monetised on RUclips I want to create a channel membership. This will be a community of lads, where everyone will get access to all Excel LADZ files and Exclusive Videos. There's been incredible interest lately, so if you want to ask me more questions about this, ask here, or on Twitter @excel_ladz 🔥
New subscriber here. Great content. Question: I entered the long formula from 3:16 and it worked, but when i drag it down only half of the players on the team show along with several " " errors. Any ideas why?
Thanks again for this super helpful video!
ive copied the formula (checked over it many times), but only one player populates and the below rows all show "SPILL). Can you assist?
do i get to keep the model forever
This is probably the best excel simulation video regarding sports I’ve ever seen. Amazing work!!!!
while its a good video ASK YOURSELF WHY would anyone put something that works on youtube ?
It would be a GREAT WAY to Lose any edge you might have. Think About It !
An analysis of the accuracy of Sportsbooks' Totals (Over/Under) Lines against actual final game score for NBA games. Thanks bro.
Thanks very much for the suggestion lad! That's an extremely popular request, so it's something that'll definitely be a video in the very near future... Make sure you're subscribed lad so that you can be notified of when it comes out!
Hello, very nice work. I work professionally in an analysis firm. I want to give you an advice. To predict the results of NBA games, you should only focus on the data of the players. You must find player strength using the average of the NBA players and their individual point averages. When reaching the average scores of individual players, you should calculate which data has the highest correlation and focus only on that data. For example; The assists statistic has a very high correlation with the average score, while shooting attempts have a very low correlation. If you are going to ask about the defensive part of the game, be sure, even we professionals cannot measure it exactly. :) I hope your channel grows because you deserve it. Good luck.
Thanks lad, I appreciate the advice. 🔥🔥🔥
Hi, I tried and did a replica of this in my excel... I put 1,000,000 simulations but the progams didn't respond hahaha then the same with 100,000 so finally I puted the 1k that the video says... I want to ask you if I can use this document as a start to make what you said? or it is necesary to make a new document from 0? Thanks in advance!
@@tenienteale Hello. Can you give me an e-mail address where I can reach you? I can send you an excel demo. Have a nice day.
@@ScoutCheff I tried 2 times to put my mail but doesnt allow me I guess; it is my nick in the google mail ;)... thanks bro in advance!
@@ScoutCheff Hi scott, may i ask your opinion of a logistic regression model for predicting the winning team as compared to the model shown above? To me, I understand the intuition behind predicting the individual scores and adding them up for comparison, but I feel like a simple comparison of net scores of each team should do the job as well. Thanks in advance!
Awesome video! One thing I am stuck on when trying to replicate this is that your player stats seem to only have player x on player y team. The data I pulled for this season is formatted so that if a player plays for multiple teams in a season there are multiple rows for each player (i.e. Mo Bamba has a row for TOT(total), a row for ORL, and a row for LAL). Was wondering if you had to deal with that for this video and if so how you manged to get it correct so that when data is refreshed it just shows the current team. Thanks!
One of the best tutorials on RUclips!!
Love the I'm having a problem with the bench players rankings I keep getting 1 I followed everything you did and everything you recommended in the comments and I'm still getting the same result hopefully you can help
I'm getting the same issue as well. Were you able to figure it out?
@@briansohangma@xcaiazzax either of you figure this out?
Hi lads! Unfortunately, there was an error when I typed in the formula under the 'Player' header in the 'Player Working' worksheet. The formula is all right, until the last ROWS part of the formula. This should actually look like:
'Raw Player Stats'$E$3:E3)))
Rather than: 'Raw Player Stats'$E$3:E4)))
That is why the first player in every team is missing, because in the video I mistakenly put E4 instead of E3.
So sorry for the confusion lads, Let me know if this fixes this problem 💪
Got to the correct result myself when started to play around and found out that Steph Curry is missing :-)
Thank you for this, when I put in this formula, it returns the same name in all cells. I’ve checked several times to ensure the formula is exact and it is. Any idea why this would happen?
Edit: Nvm, I figured it out. On the final “rows” section, instead of “$E$3:E4,” I had “$E$3:$E$4” making the end absolute, so it would only return the first name on the list.
And one final observation. Again, all from a constructive criticism from a handicapper's (or punter's) standpoint. Key variables in NBA: back-to-back road games, back-to-back home games, discretionary reduced playing time (subject to trainer, player, and coach), specialty bench positions (such as defensive matchup, three-point specialist, etc,), and long road trips (4-to-6 game road trips, quite common in NBA), injuries, and probably a few others I can't think of. These are essential variables in the handicapper's arsenal that this model cannot compute. I really enjoyed building this model. Perhaps there is some value in it. But I warn serious sports bettors to treat this model with caution.
For sure lad, there’s always more data out there 💪 I know matchups are significant, so that’s probably something I’ll add to the model in the future. Thanks for watching, sign up to the Patreon to get updates 🔥
Can you explain or is there somewhere you have already explained on how/what the numbers mean on the xFG+xFT numbers are?
So I ran model for tonight's (1/30/24) matchup IND @ BOS. Right now Vegas books have 244.5 (Over/Under). This model has 215 total points. These are the two highest scoring teams in the NBA per team scoring average. Again, not trying to mock or ridicule. This is all constructive criticism from the standpoint of a serious sports bettor. But if total points is that drastically skewed, one must assume the model is drastically skewed as well. Please tell me I'm misunderstanding something here. All the best.
Hi lad, this is the video where I made the BINOM.INV change (as explained in a response to a comment you wrote earlier): m.ruclips.net/video/yK0LmgngyuQ/видео.html
Thanks a lot of watching and commenting lad 🙏 I appreciate the support 🔥
@@excel_ladz I changed BINOM.INV. Ran simulation for tonight LAL @ BOS game. Returned Total points 221. Oddsmakers have it at 242. I really do like your model. I think it's excellent. But unfortunately it still skews heavily UNDER on point totals.
I made corrections you suggested. Model still skews heavily UNDER on point totals. Maybe if you rewrite to skew heavily OVER on point totals, you could find the middle ground. Just a thought. Best
Great Video but do you an idea Why some Formulas dont work When i try it for Example the First Formel in the Players Working Sheet
I did the players function exactly as you showed and it will not show the top players for each team ex. Tatum, Giannis Davis will not show up? any suggestions?
Hi mate! Apologies, this was a typo in the model while I was making the video. I've explained it in a comment, which I'll copy and paste here: Hi lads! Unfortunately, there was an error when I typed in the formula under the 'Player' header in the 'Player Working' worksheet. The formula is all right, until the last ROWS part of the formula. This should actually look like:
'Raw Player Stats'$E$3:E3)))
Rather than: 'Raw Player Stats'$E$3:E4)))
That is why the first player in every team is missing, because in the video I mistakenly put E4 instead of E3.
So sorry for the confusion lads, Let me know if this fixes this problem 💪
@@excel_ladz thank you appreciate it. I also have one more question don’t know if you would be able to answer but I’m using excel on a Mac and it won’t allow me to use html link for basketball reference hence it will not update daily any suggestions ?
@@almanon6151 No worries bro. I actually built the model using Excel on a Mac, so there's definitely a solution 👍 Have a look at the 'Currency Converter' video on my channel, where I run through how to establish a Power Query on a Mac. Instead of using the currency website, I used Basketball Reference 😃
i dont have what-if analysis? is there a way around it ,,, so much work so close
Awesome model. Can't wait to see what you do with player injuries and betting predictions (if you ever feel like doing that type of work).
Absolutely lad 💪, make sure you're subscribed to be notified of this video when it drops 👍.
@@excel_ladz would love to see a video on nba player prop bets
Appreciate the effort put into getting this model, about to get started.
Hey man, first off amazing video! I've learned more in this one video than I ever did in my spreadsheet class lol. @ 36:40, I am completely stumped by the formula to simulate the points. Excel keeps saying I have too many arguments, do you think you can help me out?
Hi lad, I’m glad you enjoyed 🔥 If you’re getting the ‘too many arguments’ error, there might be a comma that is missing somewhere (or even one too many commas). It may be very bland advice, but just scan through the formula in the video and write it out slowly. You’re getting an error message, not an error as a result - which is encouraging. This means that you’ve probably just have a minor typo in your formula rather than an error in your model 💪
Hey buddy. Seeing an issue with NBA simulator. If a player has a 0% statline, let’s say on 3 point shots, no matter how many times you refresh, his projected score won’t change… a good example would be the Hornets, and Mark Williams… he doesn’t shoot 3s, so his pts sim stays at 5
Hi lad, thanks for watching 🔥 The BINOM.INV function can’t compute when there’s a 0% stat line; it just amounts to an error. So what I’ve done in wrap it in an IFERROR function. If there’s a 0% stat line which computes to an error, I’ve made the projected points return a player’s average points scored throughout the season, adjusted to the amount of minutes he’s expected to play in the game. As a result, this will always stay constant.
@@excel_ladz ahhhh ok... Thanks for the explanation. Makes perfect sense. I was wondering why a couple players at the Center position (0% on 3s) would never change.
You really did great with your analysis and would like to see what you can do with the model. With injuries and day's rest (fatigue) included
Please we're waiting to see how you incorporate what I said above the model
Thanks for the support lad, I'll publish the Injuries video most likely within the next 2 weeks. It's a fairly manual process and involves checking ESPN for player injuries/rests and factoring it into the Player Minutes tab. Then, you may increase another player's minutes if they're expected to play a bigger role as a result of an injury/rest.
@@excel_ladz great, can't wait to see it
+1 That could be great to include
I really appreciate your video. I have learned more from you then any other person giving instructional videos on this topic.... My question is, In the simulated points column, on results sheet, some players points never change after sim is completed... can anyone help with this issue?
Hi lad, thanks for watching 🔥 You’ll notice at the end of the function simulating a player’s points in the match, I added in a condition with a ROUND function. Basically, the BINOM.INV function can’t handle values of 0, so of a player has a three point percentage of 0 so far, the BINOM.INV function returns an error. This results in the whole model returning an error, because you can’t include an error on the SUM function for computing a teams total points. The ROUND function essentially says that if there is an error, then return a players average points so far this season, adjusted to the minutes they are expected to play in this match. This value is constant, and as a result doesn’t change with each simulation. I hope that helps lad 👍 Things like this usually arise in the error checking portion of the model building process - very tedious stuff indeed 😂
@@excel_ladz understood..thx
Loved it, loved it, loved it, By any chance do you have anymore for the following: NFL, MLB, College Basket Ball, College football. Thank you for all that you are doing.
Thanks lad, appreciate it 💪. I'll be releasing a flurry of models in the short term, especially a beginner MLB one before the season starts... Make sure you're subscribed to be notified of these videos lad 👍.
Thank you for replying, I am noticing with the NBA Probabilities that some of the teams that I switch to in results I get the "#Ref! in the cells . Then I don't get the predicted percentage or score data ... Why is this happening ?
G’day lad, so sorry for the late response. For some reason RUclips doesn’t give me notifications for responses to existing comments which is annoying.
As to your question, this is happening because of mid-season trades and how the imported data has adjusted accordingly. There is a simple, 5 minute fix that I’ll be posting within the next couple days to rectify this issue properly lad 🔥
How is the team stats tab relative to the results tab? Like why are you trying to figure out the DEF Rating if we dont use it later on?
Hi lad, the DEF Rating is applied right at the very end of the video to scale a team’s shooting attempts based on the defensive strength of their opposition 😃
Hi, thank you so much for doing this, it looks like a great model. Can you explain how you created the line fit plot graph which gave you the numbers for xFGA+FTA? What inputs were used? Thanks!
G'day lad, I found this formula using linear regression. I took the relationship between every player's MP and FGA + FTA (using the 2022-23 season's data) and using linear regression, found the trend line; which is the quadratic formula in the cells "xFGA+FTA" you're asking about. At the 30:25 mark, the graph portraying this relationship is at the top right hand corner of the screen.
I recommend having a look at the ‘Predict Football Matches with Possession’ video on my channel, where I go through this linear regression process 👍
@@excel_ladz Thanks! I'm playing around with a way to predict players' individual stats in a game using the same simulation method to find the most likely outcome based on averages and defense. This is very helpful!
@@tristanmortensen173good morning... Can you kindly share when you are done?
And also I have issues getting the 'long formula ' can you also help with that?
Does anyone know why Giannis is listed in the raw player data table, but when I do the player column on the “Player Working” sheet he doesn’t show up? Everyone else but him does.
Hi lad! Unfortunately, there was an error when I typed in the formula under the 'Player' header in the 'Player Working' worksheet. The formula is all right, until the last ROWS part of the formula. This should actually look like:
'Raw Player Stats'$E$3:E3)))
Rather than: 'Raw Player Stats'$E$3:E4)))
That is why the first player in every team is missing, because in the video I mistakenly put E4 instead of E3.
So sorry for the confusion lad, Let me know if this fixes this problem 💪
@@excel_ladzThat’s how I have it but, when I drag it to add the rest of the players, it skips the player at E4 (which I presume is Giannis) and jumps right to Beasley at E5. After that the rest of the players are there.
Sir: Finished your video.. .all inputs are in but can't do simulations when hitting f9..only one number changed in one column,, can I send file to you? thanks bob
love this content, thanks lad
I also just simulated a game between ORL @ LAC and final total is 181. But when I sum Points Sim LAC scores 120 points, ORL 115. Round function seems to be rounding way down.
Thanks for the video. I’m running into a snag when trying to average the def rating. It’s giving me #div/0. The numbers are showing up correctly for the corresponding teams. I think it’s reading the cells as empty. I’m not sure why
Nice video, but I have problem with Player working part. I tried to put this same formula to 2023/2024 nba player stats and now some of these players repeat multiple times on Player Working tabel. Do you know how to fix it?
Can you please help me with the formula... It is not exactly working on my own end
Hi lad, this is definitely a problem. The Basketball Reference power query repeats the player’s stats for each team they play in, so names can appear more than once. I’ve solved this issue on Patreon for my members, but I reckon I’ll put up a short explainer video for all the free members on how to fix it. Basically, it just involves using the UNIQUE function in grabbing all unique players (so they don’t repeat), then matching them to their current team (while still taking into account their stats from their whole season). This then makes sure that there’s no double ups lad 👍
@@excel_ladz thank you ... I would appreciate it if there is a video explanation for us in the free membership terrain 😉😄
Good work nonetheless
Please can you show us how to get data from the website to excel the first step
Hi lad, thanks for watching 🙏. I imported the data to Excel using a power query. If you’re unsure on how to do this, check out my Power Query video I uploaded a couple months ago (the process is slightly different for Windows and Max though).
As to your question, I think I’ll upload a specific video in importing the basketball stats as a few subscribers have been having problems. Make sure you’re subscribed lad 👍
Hello, after watching this video, I am wondering if this model can be turned into a regression model that can better predict the outcomes of each game. I am new to all of this stuff so I apologize if I am wrong and this can’t happen.
Hi lad, it could be 😃 If you could devise a regression analysis with significant predictors then the model could come to an expected points figure for each team that is more accurate 👍 To then find the Win % for each team you’ll have to simulate the Expected Scorelines along an appropriate probability distribution.
Oi for scoring when u do sequence 500, what’s the difference if I were to sequence 500 the individual scoring on all the players first on the TeamResults sheet and then sum those up to get a total score.
Hi lad, if it works it works 💪 There’s plenty of different ways to come to a simulated scoreline using this model. As long as you keep the fundamental reasoning behind the model clear - that the total score for a team is the sum of its players simulated scores - then the model will work just fine 👍
For some reason the first index function keeps skipping the first player on the team in the raw stats, any idea how to fix?
G’day lad, that’s my mistake. I’ve written a comment on how to fix this 👍 Scroll through the comments, and let me know if you don’t manage to find it 🔥
41:36, I understand how to grab from trials down to 1,000...But how are you grabbing the teams as well when grabbing the next two columns?
Another question. If I want to bet individual player point totals, the model returns results with extreme variance. For example, tonight's game PHI @ POR, the model has Embid scoring 39 points. After refresh, 34 points. After another refresh, 26 points. So what number can I trust? Or would that require an entire different model to establish total points for individual player scores?
In terms of the player’s points, it’s a simulated point total. To get the probability of a player scoring under/over a certain threshold, you would need to simulate a player’s points in the model 1,000 times using a data table. This is exactly the process I did with the sum of a team’s points in the video, except you can just do it with a player’s points in this case. Then you would use a COUNTIF or SUMPRODUCT function to calculate how many trials fell under/over your threshold - dividing by the number of trials is your relevant probability.
Sorry, but one more question. I looked at all the games tonight, Monday, January, 29. The Model makes every game go Under the Vegas total by large deficits. What could that be a function of? As an example, IND and MIL both average 124.4 points per game. I plugged these teams into model, and I get a final of 105-108, for a total of 213 points. Just based on team scoring averages alone, that's off by 35 points.
Hi lad, thanks for watching 🔥 This issue was to do with the non-simulation of a player’s shots taken. This underestimated the points scored like you’ve seen in your trials. To fix this, I wrapped the BINOM.INV function for each shot type in another BINOM.INV to simulate the xFGA+FTA. This gives much more accurate over/under info. I’ll answer all your other questions under the relevant comment 👍 Most of the things you bring up have in fact been patched for a more accurate model 😃 These updates are available on the Excel LADZ Patreon (link in description).
Hello!! Huge fan of the video, extremely clear and helpful. Unfortunately, I ran into an issue I believe after my sorting function. On my players working tab, the home & away team on right hand side are both in order of players based off minutes played. But on the results tab, some teams have all 5 starters correct. But a few like the suns, have Diope and Goodwin starting but having Eric Gordon and Grayson Allen on the bench.... Or Oubre Jr starting on 76ers.. Don't quite understand why some teams have right starters but others don't. Haven't proceeded until i can figure this out. Please any insight would be super helpful. Thank you so much in advance. On top of what you have already done.
Hi lad, thanks for watching 🔥 Sometimes a player’s MP can be the same as another player on the same team. This can cause the ranks to muck up. You can fix this by adding a small, insignificant decimal to each player’s MP using the ROW function, or you can just change the MP’s in the Player Working worksheet manually so there are no repeats. There’s also some instances of players appearing for their former teams due to the formatting of the Basketball Reference dataset. If this issue is occurring lad, you should make a new column of players in the Raw Player Data worksheet using the UNIQUE function or an equivalent formula. Then you would link this column to the rest of the model to make it work. I hope these suggestions can offer some value lad. If not, then every issue/bug can be resolved by joining the Excel LADZ Patreon where you can download the model 🤠
I found two problems. First, Lakers Player Taureen Prince and D'Angelo Russel both average 30.5 minutes per game. Both play PG. So the system puts D'Angelo Russel in the lineup twice. Starting and Bench 1. Second, Basketball Reference has a position acronym PG-SG, SG-SF in the position box. That causes XLOOKUP to fail. Any thoughts on solving these two problems? I manually overwrote 30.7 minutes in Raw Player Stats for Taureen Prince and changed SG-PF to PF, and the model puts him back in the lineup. I'm assuming we'll need to make these adjustments on a case-by-case basis manually? Anyway, great model. Thanks for sharing. I learned a lot about Excel writing it.
Hello, this model is great. I am seeing some of the top players not show up on their respective teams. Example is Giannis, Anthony Davis, and Luca. Any thoughts on that?
hey mate, in the 'Player" header in Player Working worksheet, please change the ROWS to $E$3:E3 instead of $E$3:E4 (according to the model)
To get the equation for your quadratic regression did you just use MP and FGA?
Hi lad, I just ran regression using the MP as the independent x variable, and FGA+FTA as the dependent y variable. That produced the graph shown at 30:10 👍
@excel_ladz Awesome, thank you!
Thank you for your video...I inputted formulas for players working BUT got nothing in return...any help?..BY th by followed your soccer video...Great
Thanks for watching lad. If nothing is showing up, first of all make sure you are absolute referencing (according to the formulas I've done in Player Working). Also, especially if you've wrapped it in an IFERROR function to equal "", if there's an error, there's probably a minuscule error in there somewhere that just needs a slight fix (like absolute referencing).
Just rewind the video back, and if you need me to answer any more questions I will.
I just want to say you are a fking lad for these videos ! I am doing a university assignment for Data Analytics and we have use Monte Carlo, whatif analysis (E.g. goal seek, solver), breakeven analysis, hypothesis testing or regression and other statistical topics such as median/mean to tell a story about something. I want to use monte carlo to predict the probability of a team winning when their best team is performing below. HAVE YOU GOT ANY ADVICE FOR ME or which videos of yours's I can watch that will help me ? We have not learnt Poisson Distribution or anything mentioned outside of the above topics. How Would you tell a story about how we can use data analytics to predict outcomes on sportsbet ?
Any idea what I am doing wrong at 26:54, I have put the same function as you but it returns 1 for each benched player. (I have different page names than you)
=MIN(IF(ISNA(MATCH('Player Working'!$H$5:OFFSET('Player Working'!$H$5,'Player Working'!$C$5-1,0),$M$12:M18,0)),'Player Working'!$H$5:OFFSET('Player Working'!$H$5,'Player Working'!$C$5-1,1)))
Thanks in advanced love the video so far!
Hi lad, thanks for watching the video! Apologies for the late reply, I get upset with RUclips because any comments with a formula is presumed to be spam that I don't get notifications for...
Here is the correct formula for cell M18 of worksheet 'Results': MIN(IF(ISNA(MATCH('Player Working'!$H$4:OFFSET('Player Working'!$H$4,'Player Working'!$C$4-1,0),Result!M12:M16,0)),'Player Working'!$H$4:OFFSET('Player Working'!$H$4,'Player Working'!$C$4-1,1)))
If you drag that down for all the benched players lad, it should work. Let me know hot it goes 👍
Hi, im also having problems with his I tried both and for the first one I get back 1 for cell M18 and the second one give me a 0 any idea how to fix this? Thank you in advance 8 @excel_ladz a
@@excel_ladz Thanks for putting this video out, this was one of the best NBA simulation model i've seen so far but the formal for benched players still returns 1, i use google sheet, please any idea on how to correct this on google sheet
@@akintoyeakintola7845 I am also having this issue. Have you found a solution to correct this issue yet?
You figure this out?
When will the mentioned Parts 2 & 3 be coming out?
Hi lad, they’ve come out 🔥 Give it a watch 💪
Hey, quick question, rather 2 part one.
1. When sorting the player ranks in the player working sheet, the value is descending rather than ascending. Could this be due to the fact I'm using Google Sheets?
2. When entering the players in the results tab, it's not the correct starters showing up and getting an Error message when trying to enter the bench players.
Cheers Mate
you ever figure anything out one question 1? I'm running into the same problem
Hiya, currently creating this model to get experience in excel. Having one slight problem that I can't seem to solve. When sorting the players on the "Player Working" sheet all of my players are sorted from least time played to most time played. This is causing me more issues when I work on the results page. Any help or advice on how to get them sorted from greatest to least would be much appreciated. Amazing work and great tutorial. Keep up the good work!
Amazing video. Can’t tell you how much I respect and appreciate the effort you put into this! Question: I recreated the database, but the simulation page encountered a problem when players averaged the same amount of minutes. For example, Isaiah Stewart and Killian Hayes of the Detroit Pistons both averaged the same minutes per game, when it loads their rank it brings Stewart over twice and Hayes zero times. What is the solution to this problem?
Thanks for watching lad 🔥 I recommend manually changing one of the player's MP in the "Player Working" spreadsheet. For example, you know Stewart and Hayes have the exact same MP (let's say 23.2). If you want Stewart to be prioritised in the starting lineup, then you would write over his MP formula in Column B of the "Player Working" spreadsheet to 23.3, while keeping Hayes' MP exactly the same at 23.2. The formula sorting the MP in Column J would then recognise this, and so your "Simulation" page shouldn't have any issues from there.
Great video, I'm doing the part where you sort the MP with the number of players but when I sort it, it puts the highest minutes at the bottom. Do you know how to fix that?
I have some questions, the first how would you go about taking defenders defensive rating against the offensive players individually, so we can calculate how how that effects the players and therefore the teams total points. is that worth a vid or is that simple enough, if i did this video easily. Secondly how would that relate to the teams offense rating % for how that affects the individuals and the teams overall score, does that individual defensive rating equal the teams defensive rating adjusted?
Absolutely, a more advanced model would explore the fact that players have matchups in a game. This would also be a major factor in simulating rebounds. However, it’s challenging to come
up with individual defence ratings that are reliable. Definitely worth a video though.
Please how do I use the power query on excel, I am mostly interested in the basketball total points prediction. Could you maybe show me how to be
getting the stats to excel daily and process mainly for the over or under prediction.
Thanks
Hi lad, a power query is a method of importing data from a website into Excel. With a power query, the data is updated on Excel automatically as it is changed on the website, meaning you get live data daily and don’t have to manually change the stats when using the model. That’s how I got the data on my Raw Player Stats spreadsheet at the start of the video (the link to the website I got the data from is in the video description)
For my videos, I use a Mac. This means the process of doing a power query is different from a Windows computer. In order to help fix your problem lad, can I ask what computer you are building the model from?
In terms of the over/under lad, that will be a separate video uploaded within the next 2 weeks that I’m really looking forward to showing everyone.
@@excel_ladz Okay, thanks for the explanation. Please kindly do the video for just the over and under points prediction.
My system is windows 10, if you could just show the process for the power query on that points video, that would be great.
👍🏻
Any idea on how to speed up the data table process? For both my Mac and windows computer it takes about 10-15 minutes to simulate all 1,000 games. I also tried to update the data table using the update sheet feature just to see different projections and all the 1000 projected games became the same value
How accurate are the team win/loss predictions?
Hey, can you make a table like this but for football, for example, the English Premier League? But that the players are chosen manually. I would modify this one for the NBA but I am not sure what statistical data to take for each player and which formulas to use.
Maybe 7 days later NBA stats updated. Then what edit excel sheet ??
Hi lad, the data in this video is imported into Excel using a power query. This means that as the stats change every week, they are automatically changed in my Excel spreadsheet without having me going back and changing anything. If you found this video helpful lad, make sure you subscribe!
Hope you see this, appreciate the video, but I got a question.
When I lookup the players on each team, it doesnt show me players that have been traded to the team during the season.
It keeps players on the team they originally were on and shows the TOT stat line instead of the stats for their time with either team
Hopefully I explained that well enough.
Hi lad, thanks for watching 🔥 You’re absolutely right. After making the video, the dataset from Basketball Reference changed so that it repeated players who have been traded. Following this video will fix your problem lad: m.ruclips.net/video/cJOZyahy-Yo/видео.html&pp=ygUOZXhjZWwgbGFkeiBuYmE%3D
There are also other NBA Videos on the channel that have improved the model 👍
it seems the basketball players data would need to be updated daily since games are being played nearly everyday
Hi lad, the Power Query that connects Basketball Reference live data to the model makes sure that each team’s roster is completely up to date. However, if there are injuries on a team you’ll have to manually change a player’s MP to 0 in the ‘Player Working’ worksheet.
Hey man i am currently stuck on the second part of the rank
Do you have any suggestions on how to add projected assists and rebounds for each starter as well? I added an extra tab running 1000 simulations for each starter and 2 bench players to give a more accurate points total projection for the players and would like to try something similar for rebs ands assists.
what will we change if a player wont be playing that game?
Hi lad, I recommend you watch this video: m.ruclips.net/video/WBqpxYZeSU4/видео.html
Hey legend how do you do the power query at the start i can't get it the same as you. cheers legend
Could You tell me ( and please avoid the obvious answer of more is better )
what is the minimal sample size where you could say ok I think this system is good ?
300 plays ? 400 plays ? 500 plays ?
Also I am guessing it should be about 400 or 500 ? since win/lose is binary .
Last But Not least I am guessing a win % of 56% or better should be profitable ?
G’day lad, thanks for watching the video 🔥 In terms of the sample size, its whole point is to be an indicator of the simulations. So for this model, 2,000 simulations was good because the Win % would only ever fluctuate by a maximum of 1-2%. Again, if you only did 500 simulations and the model fluctuates ver little, that would be an acceptable sample size.
I hope that helps lad. I’ve set up a Twitter account where my handle is @excel_ladz, so if interested you can follow and DM me so that I can help you more personally with building sports models 🔥
Hello. Thanks for the good video. I have a question. Not all players of the team are displayed on the "player working" sheet. For example, Phoenix has no player Deandre Ayton. Although it is on the "Raw Player Stat" sheet. The formulas are the same as you have in the video. I'm updating the sheets.
I will add: on the "Player Working" sheet in the Player column, formulas are found not from the first player of the team, but from the second player. It turns out that on the "Raw Player Stats" sheet there are 18 players of the "TOR" team in the table, and the formula on the "Player Working" sheet finds only 17. How can this be fixed?
@@nighttime7849 Hi lad. It seems like the problem may be that your "Player Working" sheet is excluding the first player on every team. Because the formula I drag down at 13:20 grabs the players alphabetically, this may be the reason why only Deandre Ayton is excluded from the Suns team...
To fix this, I would try two things. Firstly, make sure that the ranges in this formula at 13:20 are appropriately absolute referenced; according to what I have done in my video. Secondly, I would make sure not to forget the +1 in the formula at stage 12:50 of the video.
Try reviewing these things, and let me know if you've made any progress lad.
@@excel_ladz All formulas are the same as you have in the video. There is still one player missing from each team in the table. Using the formula, I checked the number of players of one of the teams in the "Raw Player Stats" table. Always -1 player
@@excel_ladz I have the exact same problem, if you check your video at 15:02 you will see that the result for the Miami Heat is 15 players, but actually they are 16 in the Raw Player Stats. The formula does't get the first player from the team, in this case Bam Adebayo.
@@nighttime7849 i don't know if you managed to find the fix, but at the end of the formula it need to be: ,ROWS('Raw Player Stats'!$E$3:E3))),"") ; Hope that helps.
Good Morning : EST 8:47AM...Simulations doesn't work...everything else is fine...any help? Thanks
Hi Bob, if you are referring to the simulations in the 'Simulations' worksheet, there may be a couple of reasons as to why it isn't working. Firstly, make sure that that the range you're highlighting for the data table is the range A2:C1002. Secondly, 1000 simulations of the scoreline may take a lot of time for your computer (depending how old it is). Give your computer time to process, and maybe try clicking on the 'Refresh All' icon in the Data tab if it isn't working.
If these steps don't work Bob, you may have just entered a slightly incorrect formula. Keep trying though and send me all your questions because, unfortunately, I'm unable to receive and fix Excel files for subscribers.
Can you share the excel file please? thank you so much....
Thank you for sharing this is great. So Im stuck at the part where you copy and paste to start creating the away team in player working tab. The left side of players MP, NO of players an positions show up but the right side only a few names pop up and the rest of the cells are blank or say N/A. i cant seem to get it fixed. any suggestions will be greatly appreciated
Do you have any ways to create predictions for NCAAMB or any football?
Hi lad, thanks for watching 🔥 I'm looking to upload a complete NFL model soon, but I haven't made a college basketball model yet. Great idea though lad, I think there would be a lot of interest 💪
Hey awesome video. With the new nba season starting this week should i just wait for games to be played? Or can I make this model with old stats anyways and just swap it with new stats when enough games have been played?
Hi lad, I reckon at around 30-40 games the model can rely on only that season's data 🔥
@@excel_ladz Hello again, what should I do if 26:25 this formula comes up as 1 or 0 for the subs? I tried changing up a few things but cant seem to figure it out
@@jandomate3760 HEY man, same thing for me. Did you figure it out?
@@zachzimmer6988 Wish i could help brotha, its been a year i dont remember
Hello ladz, i dont have the what if function in google sheets is there a way to simulate that in google sheets or another way to run the 1000 sims? Or do i need to rebuild in excel(which i dont have)?
@ 42:08 can you explain whats going on, i have update version of excel (google sheets) and i cant seem to figure out how to use this in the sheet
I'm having the same problem, have you found a solution?
Is there a download link excel worksheet for this. I can't seem to replicate it without several issues. Would really appreciate
G’day lad, there is an update video I’m posting very soon that will instantly fix these issues. If you have more questions, comment again or follow me on Twitter 🔥. My Twitter handle is excel_ladz 👍
What's up man! I love all your videos. You are an excel wizard my friend! I was playing around with this concept for the WNBA and I wanted to get your thoughts. The biggest hurdle is that on basketball-reference they don't track the adjusted team metrics and also they don't label some positions the same as their NBA counterparts. For example there is not PG or SG it's just G or G-F or F-C in the WNBA stats. Regardless, I made a sheet using this setup and it seems to be working. I just wanted to see if you had any thoughts about this kind of project
Hi lad, thanks for watching 🔥 In regards to the adjusted defensive ratings, you could compute those yourself. It would involve compiling a team’s defensive rating, and then scaling it to the quality of the opposition they have played. A really easy method would be to multiply the reciprocal of the average attack rating ( so 1 / ATT Rating) a team has played all season by their own DEF Rating. For example, a team with a DEF Rating of 0.96 would be scaled down (or better) if the average quality of their opposition was above 1 (e.g. 1.02).
The positions in the model don’t matter too much, as you can always manually change a player’s expected minutes in the ‘Results’ worksheet. 👍
Btw, I just checked out your channel and it’s great lad 💪 I’ve subscribed and I reckon you’ll hit 1,000 pretty soon 🔥
@@excel_ladz Hey Thanks ! Appreciate that.
@@excel_ladz I had this problem too, except it's only returning the first player from each position, but the team is starting two forwards. How do I return two unique players at the same position into the results sheet?
Great work, how can i get this file. the link provided is not working
Hi lad, by becoming a member of the Excel LADZ Patreon you can download this file. Here is the sign up link: www.patreon.com/ExcelLADZ
the site is not going when clicked on it@@excel_ladz
cuz u got this description and depth i likey
I need a video on how to work with a power query. I’m trying to get the data from Basketball Reference, but I can’t figure out how to transfer it to the excel spreadsheet.
It's hard for me to help you greatly without knowing the brand of your computer. For example, I have a Mac, and the process of importing data using a power query is slightly different than it is on a Windows computer. If you have a Mac computer lad, you should go check out the Power Query Video I have on my channel 👍
@@excel_ladz I am using a MacBook Air laptop so I am not sure if Power Query works for laptops?
@@ScopophobiaTapes That's perfect lad, you can do a Power Query. I suggest you watch my video titled 'Excel Power Query for Mac Tutorial | Currency Converter Example'. This video will take you exactly through how to make a power query from start to finish; using an example of importing live currency data. All you have to do is change the link you're importing from: switch to Basketball Reference (in the description of this video) instead of the currency data.
Let me know how it goes lad 👍
@@excel_ladz Thank you for all your help 👍
Great content. I've tried similar for an F1 race using lap times. Only one question regarding the totals. When I try and simulate them I get the same number all the way through. It works fine when using rand() but with the sum() function it repeats the same value. Any thoughts why that happens would be appreciated
G’day lad, thanks for the support 🔥. It’s very unusual your SUM formula isn’t working. If it just stays stagnant, then there may be the possibility that the Excel Workbook is buffering and is too slow to update… Other than that, I would try to tweak the range of the formula to see if that works.
If this helped lad, make sure you’re subscribed for more 💪
I entered the formula perfectly on the players working and it’s giving me the minutes played (MP) from fewest to most. Any way to change that?
Hello, Can you really post "HOW TO DO THE POWER QUERY " to retrieve the data from the web. I have tried effortlessly to get this right and I can't. Because the way the tables are setup copied over , if it's in a table and when you try to get the average =AVERAGE(C2:C31) of all of the teams you get the #DIV/0 error no matter what. PLEASE HELP....
my excel seems as if I use a formula, I cant use the numbers I got in the formula to use in the next formula.
for example I transfered the Basketball ref defencive ratings using a formula. but then couldnt use that data for the average. it was showing #DIV/0! error
Works smoothly but somehow on the Bucks team there is just thanasis and not giannis .. Do you know what could have went wrong? Thanks tho!
Thank you for tutorial, I don’t have any previous experience working with excel or sheets, but it was easy to follow your instructions.
I have a question especially after trades happened. Players are still on roster even they been traded to other team because of stats data so if player traded he’s on 2 teams which effects team points simulation. How to react on that situation? Thank you.
G'day lad, it's very good to know relative beginners are able to follow my videos. As to your question, the data in your 'Raw Player Stats' worksheet should be imported using a power query, which grabs live data from the internet. Therefore, if a player is traded, their team will update automatically and thus you want be able to select them as part of their old team in the Team Points Simulation anymore.
If you found this helpful lad, please subscribe!
@@excel_ladz Whats the difference if I'm importing data whichs updates it real time anyway with =IMPORTHTML? Is it the same result as with power query, or it effects somehow result?
Thank you answering stupid questions :)
@@kasparasjes8370 Hi lad, I don't use Google Sheets so I'm not entirely sure of IMPORTHTML's capabilities as a function. However, I can tell you that with a power query, any data that updates on its website will be updated on your Excel spreadsheet. All you have to do is reload the page. Have a look at my 'Excel Power Query for Mac Tutorial | Currency Converter Example'. This video goes through the process of setting up a power query on a MacBook, and grabbing live currency data 🔥
imusing sheets and the import function does update when new information is added or changed
@@kasparasjes8370
I'm having an issue loading all of the teams' players using the index function. I have to keep switching from Large, Small to get players. Is there another way I can go about getting all the players from the list? Thanks
Hi , i am stuck on the part where we match players with teams i follow every step and tried multiple times but nothing shows up can anyone help me
hello excel ladiz, have you found a solution about injured or uncertain players
Hi lad, this video will be published hopefully very very soon.
@@excel_ladz thanks
I can't figure out how to link the players to the teams. I've been trying for hours. Does anyone have any advice
Is there a site wich has similar data but for soccer
Yes lad! The website is called FBref 🔥 I’ll be using this site for stats in my next video 👍
Hey! I finally got to finish the excel file! such a jorney this afternoon! But now I have a question, how can you perform all simulations not for the expected lineup, but when you already know the lineups for the game. Im not sure if I mide myself clear.... Thank you very much
Hey lad, I’m glad you finished the file. Ideally within the next 2 weeks, I’ll post a video explaining how to adjust Player Minutes in the model. So as you said, if you already know how many minutes a player will play in a game (say it’s an hour before the game and the squads have been confirmed), then you can take that into account so it will affect the simulations accordingly. Basically, it involves manually changing the Player Minutes stats in the Player Working worksheet. I hope that helps lad.
This week right? I can't wait 💪💪
@@costanbatips Yep, I'm glad you're looking forward to it lad 👍
Hey Master! We are still waiting!! :D When it will happen? Thank you very much!
Didn’t work for me the first time I put in an away and home team it works but any other time it says #N/A and for the Bskt. Ref . Def it said #N/A for half the teams😢 can someone please tell me how to fix it😢😢😢
very insightful! can we download this file by any chance?
I’m glad you enjoyed it lad, make sure you’re subscribed 👍. In the very short term I’m considering making a website on which all my models and courses will be available to purchase. Would that be something you’d be interested in lad?
@@excel_ladz depends on the price
When i go to sort the players by minutes played, it goes least minutes down to most minutes so it messed up the starters later on. i copied the sort function exactly. any ideas?
Sorry, LADZ, but the tied minutes glitch is really annoying. For my BRK predictive model it put the same player in lineup again. That's now twice on two occasions in one night. Easy fix is to manually tweak MPG AVG in raw data set. But this is annoying. I think you have a brilliant model, but skewed totals, tied minutes error, variance in Sim. Pts. (every time each player refreshes, wildly differing point totals), injuries, trades, resting games (i.e. reduced minutes on single or multiple games due to fatigue or soreness and completely discretionary for coach and player) create too many painstaking variables to make sheet practical for every day betting purposes. Now I may be completely wrong, but I'd like to see some serious data using this system against Las Vegas odds, Spread, Total (Over/Under), and Moneyline with at least a 1000 games. I can already tell Total (Over/Under) is going to be deeply skewed UNDER on every game. So that data will be useless. Moneyline and Spread would be interesting.
Hi lad, you can fix this issue by adding + ROW()/10000 at the end of every player’s MP in the player working worksheet (column B). This separates players by an insignificant decimal, so there can be no tie (and subsequently no error) in the model for MP. The PG - SG thing is annoying l, but could be solved by using the FIND function or some combinations of text functions that would return only the exposition on the left for that player.
How to update the database with each round?
Because you are importing the data with a power query, simply press the ‘Refresh All’ button in the Data tab of Excel and your database will update with new player and team stats correct to that day.
Hey lad, I've done everything as I should (I believe, I double-checked) but I ran into two issues: 1) when the player is averaging 0 attempts per game, for example, Ron Harper Jr. for Toronto Raptors this season, the simulation shows Div/0 error. 2) All point simulations are waaaay too low, oscillating between 50-70 points!
I "fixed" the first problem with the extra IF [expected attempts]=0 then 0, if not true then the formula for Poss. and the simulation, but the second issue of simulated scores being way too low still's there.
Hi lad, I’m glad you figured out how to patch the Ron Harper Jr situation. There might be an error with one part of your binomial simulations if you find the simulations to be too small. For example, the sum of free throws might not be calculating correctly. Without making any player changes, I put Lakers vs Bulls into the model for tomorrow’s game. The Lakers should have an approximately 56% chance with an expected score of 110. Bulls 44%, with expected score of 106. How far off the points sim totals for this match are you lad?
@@excel_ladz Thanks for reply, lad. I'm way off on the simulation for Lakers - Bulls tomorrow. I've got Lakers at 63 and Bulls at 57. Everyone's points seem to be off - James simulated for 9, Wood, Christie, Vanderbilt and Hayes all predicted for 0. On the Bulls side points are all low as well - i.e Vucevic predicted for 6... I'm not sure where's the issue. Maybe I've missed something but I double checked with your video and it looks to me like it's the exact same.
The function written is:
=IF($F27=0,0,IF(D27>0,IFERROR(2*BINOM.INV(BINOM.INV(F27,G27,RAND()),H27,RAND())+3*BINOM.INV(BINOM.INV(F27,I27,RAND()),J27,RAND())+BINOM.INV(BINOM.INV(F27,K27,RAND()),L27,RAND()),ROUND((F27/E27)*XLOOKUP(B27,players_data_raw!$B$3:$B$1017,players_data_raw!$AD$3:$AD$1017),0)),0))
I hope this is user error, but my feeling is ROUND FUNCTION or RAND FUNCTION is skewing data. But the total scores this model produces are skewed nearly 25-50 points in some cases. Here are last night's model forecasts against actual gametime scores:
MODEL ACTUAL
LAC 86 108
CLE 101 118
NOP 99 108
BOS 108 118
UTA 92 114
BRK 102 147
I would list them all, but obviously three is enough to demonstrate something is seriously off. Not trying to be critical. I love the model. But I bet real dollars of hard-earned money. I like to bet totals (OVER/UNDER). But something is seriously off in this predictive model. Given your demonstrated brilliance with Excel, I'm sure you'll find a solution. Or perhaps it's that old juggernaut STANDARD DEVIATION, which has been the eternal curse of all predictive models using probability. But clearly Pt. Sim. column and ROUND FUNCTION are skewed severely towards low-scoring totals.
hi Excel LADZ do the numbers in the formula cells "xFGA+FTA" have to be updated season after season or is it timeless? How did process to get them ? thanks you very much
G'day lad, I found this formula using linear regression. I took the relationship between every player's MP and FGA + FTA (using the 2022-23 season's data) and using linear regression, found the trend line; which is the quadratic formula in the cells "xFGA+FTA" you're asking about. At the 30:25 mark, the graph portraying this relationship is at the top right hand corner of the screen.
This formula doesn't need to be updated season by season, as it stays relatively the same throughout time. Any change in the formula to reflect current trends would result in the smallest of changes to the "xFGA+FTA", which would be insignificant to the final result of the model.
@@excel_ladz very clear thank you 👍
Hi... I'm trying to recreate this, I'm at the minute 22 of the video... I have had to learn a lot, from how to export a table from internet to excel, to a lot of functions... and I'm from Chile, so I have a lot of problems with the data cause we use , to decimals, and all the excel does not understand very well . as decimals... but I have tried some solutions and have worked... but I have a question, cause there is players with more than 1 team, and to the "minutes per game" it takes the higher number... I tried to solve but I coudn't... I'm a home user, not a pro so I tried to put some IF but I couldn't solve it... so can you help me to resolve that? what I have to add to the statistics is not the higher, but the respective to the team! Thanks in advance and for the video!
Hi lad, I’m very glad that you’ve raised this issue. I uploaded the video before the NBA trade period, and so you wouldn’t of had any issues with this step then. However, when players changed teams during the trade period, the dataset created a new column instead of just changing the team value…
To fix this problem for subscribers I’ll be uploading a very simple solution video on the next couple days. Make sure you’re subscribed to be notified immediately of this 👍
@@excel_ladz As I told u, I'm making this... and I have a problem... today pacers are playing against the rockets... and in the rockets kevin porter has the same MP as jalen green (35.5).. so in the rank colums in the results sheet, both of them appeared with 1... I investigate and add this to the function: +COUNT.IF('Player Working'!$J$5:J5;XLOOKUP($B13;'Player Working'!$I$5:$I$30;'Player Working'!$J$5:$J$30))-1... My players in result started in B13, you in B12... tell me if thats ok to dont repeat any rank please?... and other problem that I haven't found a solution is josh christopher is from houston rockets, but my "player working" sheet doesn't recognize him as a rocket's player... I don't understand why and I don't know if this repeat for other player in other team... I don't know what to do. That my inform for today hahaha Greetings!!!
@@tenienteale G'day lad, I assume after watching my updated video that your problem regarding Josh Christopher being on the wrong team is solved. However, there is still your Rank issue which I'll address below.
Due to the way this model is designed, it's not okay to have two (or more) players with the same Rank. The model will make a mistake in making total minutes played more than 240, which is unfair for the other team. Below is the solution:
In the MP column (Column B) of the Player Working sheet, you can manually change a Player's Minutes Played so that two players don't have the same number. Just write over the formula with the Player's Minutes + 0.1 (so there's no material difference). For example, if Irving and Doncic both had 33.4 MP, then you can leave Irving. For Doncic's MP, write over the top 33.5, and then the ranks will be updated accordingly.
Let me know how this goes lad 👍
@@excel_ladz I made that exactly solution few weeks ago ahahha I followed u in twitter :D
Can you do one for NRL
do you factor somehow for the opponent or do you sample a team's points solely based on it's players' stats regardless of the opponent? I don't think you do, but perhaps I missed sth important.
Right at the end of this video there is a section called "Defensive Adjustment". This takes into account a team's defensive strength, and adjusts their opponent's scoring opportunities based off of that. For example, a defensive rating of 1.05 (which the Spurs had), is bad. This defensive rating is multiplied by their opposition's xFGA+FTA, so that the other team has more opportunities to score because of the Spurs' poor defence.
Ofcourse, I totally missed that thanks
How do I import the data?
Hi lad, thanks for watching 💪 You need to import the data into Excel using a Power Query. The links to the Basketball Reference websites are in the description of this video 👍
Hello! Excelent work, do you have that excel file to share? Thank you
Thanks for the support lad. Unfortunately, I don't share my Excel files with subscribers. Instead, I prefer to give a full tutorial (from scratch) on how to build it.
If you have any specific questions or problems you might run into building the model on Excel, comment on the video and I'll get back to you as soon as possible lad.
how do you remove duplicate nba players? A player appears three different times because he was traded.
I just uploaded a video to solve your problem lad 🔥 Thanks for watching! I hope the model helps 💪