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

Жыл бұрын

Can we download this file that you made? An make updates to it ourselves??

@excel_ladz

Жыл бұрын

@@blackroyaltymgmt5195 Hi lad, as soon as I'm monetised on KZread 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 🔥

@emilioangeles4389

Жыл бұрын

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!

@peter93263

Жыл бұрын

ive copied the formula (checked over it many times), but only one player populates and the below rows all show "SPILL). Can you assist?

@mathewdasilva4421

5 ай бұрын

do i get to keep the model forever

This is probably the best excel simulation video regarding sports I’ve ever seen. Amazing work!!!!

@alivstyson1864

2 ай бұрын

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 !

love this content, thanks lad

One of the best tutorials on KZread!!

Appreciate the effort put into getting this model, about to get started.

An analysis of the accuracy of Sportsbooks' Totals (Over/Under) Lines against actual final game score for NBA games. Thanks bro.

@excel_ladz

Жыл бұрын

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!

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).

@excel_ladz

Жыл бұрын

Absolutely lad 💪, make sure you're subscribed to be notified of this video when it drops 👍.

@vipevil2492

7 ай бұрын

@@excel_ladz would love to see a video on nba player prop bets

cuz u got this description and depth i likey

Loving it!

@excel_ladz

Жыл бұрын

Glad you like it lad

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!

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

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?

@excel_ladz

6 ай бұрын

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 💪

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.

@excel_ladz

Жыл бұрын

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 👍.

@rickycoolige472

Жыл бұрын

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 ?

@excel_ladz

Жыл бұрын

G’day lad, so sorry for the late response. For some reason KZread 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 🔥

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

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

@hybridinc1035

Жыл бұрын

Please we're waiting to see how you incorporate what I said above the model

@excel_ladz

Жыл бұрын

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.

@hybridinc1035

Жыл бұрын

@@excel_ladz great, can't wait to see it

@MrKishkuman

Жыл бұрын

+1 That could be great to include

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

6 ай бұрын

Hi lad, I reckon at around 30-40 games the model can rely on only that season's data 🔥

@jandomate3760

6 ай бұрын

@@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

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.

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.

@excel_ladz

Жыл бұрын

Thanks lad, I appreciate the advice. 🔥🔥🔥

@tenienteale

Жыл бұрын

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!

@scoutmatchanalyst3264

Жыл бұрын

@@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.

@tenienteale

Жыл бұрын

@@scoutmatchanalyst3264 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!

@thomas6756

7 ай бұрын

@@scoutmatchanalyst3264 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!

Wish I saw this at the beginning of the season!! do you have one like this for MLB?

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, 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!

@excel_ladz

7 ай бұрын

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 👍

@tristanmortensen173

7 ай бұрын

@@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!

@yusufjimoh194

6 ай бұрын

​@@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?

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

@excel_ladz

Жыл бұрын

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 💪

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

so i've run the index for players a dozen times and tried tweaking several items but it keeps giving me the first player on the Raw Player Stats sheet every time. It's not indexing based on the team selected from the Results sheet. Precious Achiuwa is the first player on the list and I just keep getting his name and not a player from the team selected. Any help?!?!

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

@briansohangma

5 ай бұрын

I'm getting the same issue as well. Were you able to figure it out?

@TL8706

4 ай бұрын

@@briansohangma@xcaiazzax either of you figure this out?

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?

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!

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

@excel_ladz

Жыл бұрын

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 🔥

@know_the_game

Жыл бұрын

@@excel_ladz Hey Thanks ! Appreciate that.

@tristanmortensen173

9 ай бұрын

@@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?

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 💪

@CureForLove

6 ай бұрын

Got to the correct result myself when started to play around and found out that Steph Curry is missing :-)

@ndavis265

4 ай бұрын

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.

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

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)?

Good vid lad

@excel_ladz

Жыл бұрын

Let's trot lad

@yeetyeeter4779

Жыл бұрын

@@excel_ladz pulled up in a vl turbo

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.

@excel_ladz

5 ай бұрын

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 🤠

Hey legend how do you do the power query at the start i can't get it the same as you. cheers legend

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

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 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?

@excel_ladz

6 ай бұрын

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.

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?

@excel_ladz

4 ай бұрын

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.

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

@excel_ladz

Жыл бұрын

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.

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.

@excel_ladz

4 ай бұрын

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 🔥

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

@excel_ladz

Жыл бұрын

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.

@costanbatips

Жыл бұрын

This week right? I can't wait 💪💪

@excel_ladz

Жыл бұрын

@@costanbatips Yep, I'm glad you're looking forward to it lad 👍

@costanbatips

Жыл бұрын

Hey Master! We are still waiting!! :D When it will happen? Thank you very much!

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.

@excel_ladz

6 ай бұрын

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 👍

i dont have what-if analysis? is there a way around it ,,, so much work so close

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.

@excel_ladz

5 ай бұрын

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.

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.

@excel_ladz

Жыл бұрын

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!

@kasparasjes8370

Жыл бұрын

@@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 :)

@excel_ladz

Жыл бұрын

@@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 🔥

@user-ic1ig5vu8o

4 ай бұрын

imusing sheets and the import function does update when new information is added or changed @@kasparasjes8370

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

@excel_ladz

Жыл бұрын

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.

@MrKishkuman

Жыл бұрын

@@excel_ladz very clear thank you 👍

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.

@excel_ladz

Жыл бұрын

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.

@basiliszag

Жыл бұрын

Ofcourse, I totally missed that thanks

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?

@excel_ladz

4 ай бұрын

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 😂

@bobbyjonesjr4049

4 ай бұрын

@@excel_ladz understood..thx

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.

@excel_ladz

4 ай бұрын

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).

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

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.

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!

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 ?

@excel_ladz

Жыл бұрын

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 🔥

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. 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.

@nighttime7849

Жыл бұрын

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?

@excel_ladz

Жыл бұрын

@@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.

@nighttime7849

Жыл бұрын

@@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

@mdaaa783

Жыл бұрын

@@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.

@mdaaa783

Жыл бұрын

@@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.

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.

How accurate are the team win/loss predictions?

Hello! Excelent work, do you have that excel file to share? Thank you

@excel_ladz

Жыл бұрын

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.

I can't figure out how to link the players to the teams. I've been trying for hours. Does anyone have any advice

Thanks for the comprehensive tutorial! would be very interesting to know how to adjust dynamically if the player is not available or injured?

@excel_ladz

Жыл бұрын

Absolutely lad, I'll upload this video very soon. Make sure you're subscribed to be notified when this vid drops!

@ 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

@antoniorodriguez6010

5 ай бұрын

I'm having the same problem, have you found a solution?

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?

@fitnforty

4 ай бұрын

me too. have you found a fix yet?

When will the mentioned Parts 2 & 3 be coming out?

@excel_ladz

6 ай бұрын

Hi lad, they’ve come out 🔥 Give it a watch 💪

Do you have any ways to create predictions for NCAAMB or any football?

@excel_ladz

Жыл бұрын

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 💪

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

@excel_ladz

Жыл бұрын

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.

@boldbuzz

Жыл бұрын

@@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. 👍🏻

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?

@yusufjimoh194

6 ай бұрын

Can you please help me with the formula... It is not exactly working on my own end

@excel_ladz

6 ай бұрын

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 👍

@yusufjimoh194

6 ай бұрын

@@excel_ladz thank you ... I would appreciate it if there is a video explanation for us in the free membership terrain 😉😄 Good work nonetheless

For some reason the first index function keeps skipping the first player on the team in the raw stats, any idea how to fix?

@excel_ladz

7 ай бұрын

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 🔥

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

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

@whe8els

4 ай бұрын

you ever figure anything out one question 1? I'm running into the same problem

@fitnforty

4 ай бұрын

me too!@@whe8els

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?

@excel_ladz

7 ай бұрын

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.

@excel_ladz great video! Question the formula @26:40 I copied exactly but I'm only getting 0 as a return, any help?

@excel_ladz

6 ай бұрын

Thanks lad, can I ask if the cell above it (M18) is working? If so, then just be careful that you put $M$12:M18 when updating the ranges properly 👍

@marquanbowman3586

6 ай бұрын

That cell is also returning 0@@excel_ladz

@TheJoshrilla

5 ай бұрын

@@excel_ladzI cannot get M18 to work. When I hit enter, it performs an “open file” search on my computer! Thanks for everything!

@TL8706

4 ай бұрын

@@marquanbowman3586did you ever figure this out? I’m having the same probem

@carloscordoba9777

4 ай бұрын

Hi did you get this resolved? Mine is throwing 1 as the result

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!

@excel_ladz

Жыл бұрын

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 👍

@tenienteale

Жыл бұрын

@@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!!!

@excel_ladz

Жыл бұрын

@@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 👍

@tenienteale

Жыл бұрын

@@excel_ladz I made that exactly solution few weeks ago ahahha I followed u in twitter :D

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?

@excel_ladz

3 ай бұрын

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 😃

Hey @excel_ladz I am using google sheets do you know of anyway to get the trials like you did around the @42:00 mark

@antoniorodriguez6010

5 ай бұрын

I'm having the same issue, have you found a solution?

@marquanbowman3586

5 ай бұрын

@@antoniorodriguez6010 I have not

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.

@excel_ladz

4 ай бұрын

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 💪

@ndavis265

4 ай бұрын

⁠@@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.

Can you do one for NRL

very insightful! can we download this file by any chance?

@excel_ladz

Жыл бұрын

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?

@ayrtong171

Жыл бұрын

@@excel_ladz depends on the price

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

@Gmel405

3 ай бұрын

Hopefully I explained that well enough.

@excel_ladz

3 ай бұрын

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.kzread.info/dash/bejne/lX6DvNuamNyTidI.html&pp=ygUOZXhjZWwgbGFkeiBuYmE%3D There are also other NBA Videos on the channel that have improved the model 👍

To get the equation for your quadratic regression did you just use MP and FGA?

@excel_ladz

7 ай бұрын

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 👍

@JacobMcCacken

7 ай бұрын

@excel_ladz Awesome, thank you!

Is there a download link excel worksheet for this. I can't seem to replicate it without several issues. Would really appreciate

@excel_ladz

Жыл бұрын

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 👍

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

@excel_ladz

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.

@bocline3981

5 ай бұрын

@@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.

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.

@excel_ladz

4 ай бұрын

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.kzread.info/dash/bejne/q39krs-gnsrfpbQ.html Thanks a lot of watching and commenting lad 🙏 I appreciate the support 🔥

@JimSmothers

4 ай бұрын

@@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.

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?

@fitnforty

4 ай бұрын

me too! have you found a solution?!

hello excel ladiz, have you found a solution about injured or uncertain players

@excel_ladz

Жыл бұрын

Hi lad, this video will be published hopefully very very soon.

@nap1346

Жыл бұрын

@@excel_ladz thanks

I am trying to build this and am having some issues. I attempted to get the players to populate and the formula is skipping the first player on the team in the list of players. What am I doing wrong? Also when I attempted to get the minutes rank to populate the starters worked fine but i keep getting 1 as an answer. I have checked and checked and checked and can not figure out why its not working. Any help would be awesome!

@Mbosko1289

5 ай бұрын

This is my player look up formula... =iferror((index('Raw Player Stats'!$B$3:$B$1001,small(if('Raw Player Stats'!$E$3:$E$1001=xlookup(Results!$E$2,Teams,Team_ID),row('Raw Player Stats'!$E$3:$E$1001)-row('Raw Player Stats'!$E$3)+1),ROWS('Raw Player Stats'!$E$3:E4)))),"")

@Mbosko1289

5 ай бұрын

and this is my attempting to pull bench players ranks formula... =min(if(isna(match(Working!$H$6:offset(Working!$H$6,Working!$C$6-1,),M12:M16,0)),Working!$H$6:offset(Working!$H$6,Working!$C$6-1,1)))

Good Morning : EST 8:47AM...Simulations doesn't work...everything else is fine...any help? Thanks

@excel_ladz

Жыл бұрын

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.

@costanbatips

Жыл бұрын

Can you share the excel file please? thank you so much....

How do I fix getting an #VALUE error in the PTS SIM column? It appears to only happen when a player has 0% or 100% in a shooting percentage.

@philippedenis3231

7 ай бұрын

I have the same issue everything works including sim unless a player on roster has zero. how do i fix this?

@excel_ladz

6 ай бұрын

Hi lad, make sure that you’ve included the IFERROR function in your formula. This should take care of any instances where there is a 0% in the row and will instead return a player’s average points adjusted by their expected minutes played for the game. If you’re still getting an error here, it might be because a player has 0 expected minutes played. In this situation, you may have to manually write over the top of that cell (eg put the number 2 to represent 2 expected minutes played which is pretty insignificant) so that the model can run its calculation without an error. I hope this helps a bit lad 👍

@brettbosse7174

6 ай бұрын

@@excel_ladz I believe I have transcribed the formula properly...am I missing something? =IF(D12>0,IFERROR(2*BINOM.INV(BINOM.INV(F12,G12,RAND()),H12,RAND())+3*BINOM.INV(BINOM.INV(F12,I12,RAND()),J12,RAND())+BINOM.INV(BINOM.INV(F12,K12,RAND()),L12,RAND()),ROUND((F12/E12)*XLOOKUP(B12,'Raw Player Stats'!$B$3:$B$1065,'Raw Player Stats'!$AD$3:$AD1065),0)),0)

@ohlawdy1986

5 ай бұрын

@@excel_ladz Great video! I'm also getting this error when there's someone with either 0% or 100% in one of their rows. The IFERROR doesn't seem to do anything with it and I can't find a formula that will change it. Is it possible to do one so that if XLOOKUP finds a value of 0% then it shows as 1% or if it finds 100% then it shows 99%?

Great work, how can i get this file. the link provided is not working

@excel_ladz

4 ай бұрын

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

@kabiru76

4 ай бұрын

the site is not going when clicked on it@@excel_ladz

Is there a site wich has similar data but for soccer

@excel_ladz

6 ай бұрын

Yes lad! The website is called FBref 🔥 I’ll be using this site for stats in my next video 👍

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.

@excel_ladz

Жыл бұрын

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

Жыл бұрын

@@excel_ladz I am using a MacBook Air laptop so I am not sure if Power Query works for laptops?

@excel_ladz

Жыл бұрын

@@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 👍

@ScopophobiaTapes

Жыл бұрын

@@excel_ladz Thank you for all your help 👍

17:13 what’s up bro.. really digging your chart, but im having a difficulty at this very spot…every other problem i was able to rewrite the formula but this particular one exactly.. its not showing most mins to least.. its showing least mins to most.. and when i move on to the next phase.. i could see how much this formula doesnt put the starting players on team sheet… and i wrote the formulas exactly =sort(cell:offset(cell:$cell -1,1),(2),-1) big up from NY

@excel_ladz

Жыл бұрын

Thanks for commenting lad, if you liked the video please consider subscribing to the channel. As to your problem, I would say the first thing to check is if you wrap the 2 in the formula with braces (which are these { } "curly brackets") rather than normal brackets that look like this: ( ). So your formula should look like this (assuming the 'cell' you describe is A5): =SORT(A5:OFFSET(A5,$C$5-1,1),{2},-1). Try this (making sure you absolute reference properly, e.g. $C$5), with the curly brackets, and let me know if it solves your problem lad.

@disispeter

Жыл бұрын

@@excel_ladz it didn’t change at first, but i started playing around with the formula & once i removed the -1 at the end, it fix it for the better.. thanks homie, I’ll be on your page checking out different spread sheets of how to beat the books much love

@excel_ladz

Жыл бұрын

​@@disispeter I'm glad it worked lad, and thanks for the support.

@graysonvanderburg4586

4 ай бұрын

@@disispeter Can you show me what you did? Currently having the same problem and can't seem to quite figure it out. The players keep displaying themselves from least to greatest and I have the same exact formula as @excel_ladz.

Please can you show us how to get data from the website to excel the first step

@excel_ladz

Жыл бұрын

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 👍

how do you remove duplicate nba players? A player appears three different times because he was traded.

@excel_ladz

Жыл бұрын

I just uploaded a video to solve your problem lad 🔥 Thanks for watching! I hope the model helps 💪

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!

@excel_ladz

Жыл бұрын

Hi lad, thanks for watching the video! Apologies for the late reply, I get upset with KZread 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 👍

@luisjovel2678

7 ай бұрын

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

@akintoyeakintola7845

7 ай бұрын

@@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

@johnhowy4823

6 ай бұрын

@@akintoyeakintola7845 I am also having this issue. Have you found a solution to correct this issue yet?

@TL8706

4 ай бұрын

You figure this out?

Ur a fucking legend bro

it seems the basketball players data would need to be updated daily since games are being played nearly everyday

@excel_ladz

5 ай бұрын

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.

The command at 11:30 does not include the first player on the team for any like steven adams was not included, any way to fix that?

@jacobeppele3158

Жыл бұрын

Nor was Bam for Miami

@excel_ladz

Жыл бұрын

Thanks for watching the video lad, make sure you’re subscribed 👍. I’ve had another viewer ask the same question, and it’s very tricky to work out what the root of the problem is. First things first, make sure your absolute referencing is exactly how I’ve done it. Then check the ranges. If you’ve imported your data differently to how it’s formatted in the video, adjust accordingly. Keep me updated on your progress lad, as this is a common problem people are having and I want to make sure the error is identified. Very soon I’m uploading a video on how to quickly fix the imported data so that it updates correctly for players who have been traded across teams mid season. Stay tuned lad!

@excel_ladz

Жыл бұрын

@@jacobeppele3158 Hi lad, we've narrowed down the problem. 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, I hope I haven't caused too much trouble 👍 Let me know if this fixes this problem 💪

How to update the database with each round?

@excel_ladz

Жыл бұрын

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.

what will we change if a player wont be playing that game?

@excel_ladz

4 ай бұрын

Hi lad, I recommend you watch this video: m.kzread.info/dash/bejne/iXal0tqSisi5hZc.html

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!

@jakubtajdus1841

5 ай бұрын

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.

@excel_ladz

5 ай бұрын

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?

@jakubtajdus1841

5 ай бұрын

@@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))