Excel LADZ
patreon
Solving Players Being Duplicated | NBA Model Update
🕑 Added 2023-12-03 05:46:27 +0000 UTCG'day lads,
In the video above I run through how I solved a small issue with the Excel LADZ NBA Model. In some teams, players like Jimmy Butler were showing up twice; once in the starting lineup and once on the bench. This is obviously an error, and has been fixed. The Excel LADZ NBA Model files on the original post have been updated, and will not show this error again π The models are also included as attachments to this post lads.
The issue was two players were given the same rank, as their Minutes Played (MP) was the same. This caused the RANK.EQ function to give players the same rank, and literally skip the next rank. This 'skipped' rank was then included as the first player on the bench. This would cause a player to appear twice, if their MP exactly matched somebody else's on the team.
To fix this, I used the ROW() function to add a very small decimal (insignificant to the final result) to each player's MP in the 'Player Working' spreadsheet. This way, no player's MP would match another's (while the integrity and efficiency of the model is maintained).
If you have any questions lads let me know! πͺ
Comments
Swq Qwerty
Hi laD, Once you configure Power Query to mark players that are out you can take it to the next level if you want and assign substitute number for player that is out, so if player made 30 PPG most likely that game will be missing some points, I did this and it works, just fyi I did not take into into consideration players with 8 or 9 points or less, if player makes more than 8/9 points model will deduct points from the score, Its scary how close it gets to the final score if lets say you have 2 or 3 major players out...just some food for thought...
Swq Qwerty
it did not display all text , ill repost
Swq Qwerty
Hi laD, Once you configure Power Query to mark players that are out you can take it to the next level if you want and assign substitute number for player that is out, so if player made 30 PPG most likely that game will be missing some points, I did this and it works, just fyi I did not take into into consideration players with <9 PPG, so if player makes less than 8 /9 points model will do nothing, but if player makes 8/9>points model will deduct points from the score, Its scary how close it gets to the final score if lets say you have 2 or 3 major players out...just some food for thought...
Excel LADZ
Thatβs a great idea. Iβll check it out and see if I can add it in through a Power Query π
Swq Qwerty
Hi Lad, Maybe for future update, basically I downloaded injured players list from nba reference then I match 2 names and if there is an out word by 2 matched player model would put X by that players name, it works very well and its easier to see who is out, I also incorporated conditional formatting so if there is a 2 name match it would highlight that player.... =IF(AND(B12<>"", ISNUMBER(MATCH(B12, $R$13:$R$135, 0)), ISNUMBER(SEARCH("Out", INDEX($U$13:$U$135, MATCH(B12, $R$13:$R$135, 0))))), "X", "")
Nicolas Gazeau
Hi man ty for your return, it still doesn't work, I can't understand why I've tried everything I could but I'm probably not good enough at Excel to understand where the problem is coming from. What's your discord buddy ?
Excel LADZ
Hi lad, I actually build all the spreadsheets on my MacBook Pro so that shouldnβt be the issue. Firstly, make sure when youβre updating the model, all you need to do is go into the βDataβ tab and select the βRefresh Allβ icon. Wait for the Power Query to refresh, and then all player data will be updated. Secondly, try downloading the βLegacyβ file. It might be a bit slower to load, but it works for all Excel versions 2010+. If youβre still experiencing issues lad, definitely send a screenshot on Discord π€ πͺ
Nicolas Gazeau
the formula that no longer works when I update the data is that of Minutes played (MP), ps: I'm using a MacBook Pro
Nicolas Gazeau
Hi, I have a little problem, when I update the statistics I no longer have anything in the "player working" sheet there is an error, which causes everything else to be blocked, I can send you some screenshots if you want
Mark Callaway
Was more simply. I only have to change the , and . of my Excel. thanks all working 100%
Excel LADZ
Hi lad, is this error taking place in the 'Raw Player Data' worksheet? This might suggest Excel's connection to the Power Query has been disrupted. Shoot me a message through Patreon or Discord if you're unable to solve this problem π
Mark Callaway
Man, I have a problem, when I refresh all, the data gone, show me only wrong data
