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
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
2023-12-31 03:25:00 +0000 UTCit did not display all text , ill repost
Swq Qwerty
2023-12-31 03:22:57 +0000 UTCHi 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...
Swq Qwerty
2023-12-31 03:20:38 +0000 UTCThat’s a great idea. I’ll check it out and see if I can add it in through a Power Query 👍
Excel LADZ
2023-12-31 02:15:44 +0000 UTCHi 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", "")
Swq Qwerty
2023-12-29 20:19:01 +0000 UTCHi 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 ?
Nicolas Gazeau
2023-12-28 05:05:09 +0000 UTCHi 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 🤠 💪
Excel LADZ
2023-12-28 04:55:14 +0000 UTCthe 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
2023-12-27 15:14:34 +0000 UTCHi, 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
Nicolas Gazeau
2023-12-27 14:46:27 +0000 UTCWas more simply. I only have to change the , and . of my Excel. thanks all working 100%
Mark Callaway
2023-12-12 02:04:44 +0000 UTCHi 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 👍
Excel LADZ
2023-12-11 23:31:00 +0000 UTCMan, I have a problem, when I refresh all, the data gone, show me only wrong data
Mark Callaway
2023-12-11 21:40:01 +0000 UTC
