Excel LADZ
patreon
Excel LADZ - NBA Rebound Model
🕑 Added 2024-02-08 15:00:10 +0000 UTCG’day, lads
In this article I’ll explain how I built the Excel LADZ NBA Rebound Model. A video tutorial on how to use the model is above. If you have any questions, please leave a comment!
JOIN the best Excel Sports Modelling Community by becoming a member of the Excel LADZ Patreon! Receive downloadable access to every model (including the Excel LADZ NBA Rebound Model) as well as technical posts, like this, which will improve your understanding of sports modelling. You also have the ability to directly message me lads, so I can help you with any questions/problems!
Download NBA Rebound Model: https://www.patreon.com/posts/excel-ladz-nba-98513859
Collecting Data
Before I built the model, I thought about the stats that would best predict the number of rebounds a player will get. Obviously, the player’s average number of rebounds is the most reliable insight. However, adjustments must be made to this number to account for:
- A stronger/weaker opponent
- The projected PACE of the game
For example, the Washington Wizards currently have the lowest Defensive Rebound % in the league (70.7%). As a result, their opposition can expect to gather a greater number of Offensive Rebounds. On the other hand, the New York Knicks have the greatest Offensive Rebound % at 29.4%. Therefore, a player/team would be expected to grab less Defensive Rebounds in that match.
The PACE of a match is also important, as it determines the number of Rebounds available in that match. Currently, the Miami Heat and the Denver Nuggets average the least amount of possessions per game in the NBA. As a result, if the two squads were to play, the expected number of possessions in the game would be relatively low. This decreases the number of rebounds in the game.
The two factors above, and their influence on a player’s expected rebounds, can be captured in a Rebound Model by adding in the following stats to the model (links to data source at the bottom of the article):
- NBA Player Per Game Stats - To grab a player’s average Rebounds and other info
- NBA PACE Stats - To estimate the number of possessions in the match, relative to the league average
- NBA Team Offensive Rebound % - To scale up/down a player’s Expected Defensive Rebounds
- NBA Team Defensive Rebound % - To scale up/down a player’s Expected Offensive Rebounds
Data Cleaning
Often the data you collect in Excel has to be ‘cleaned’. That is, sorted in a way that your model is able to process. In my case, I wanted my final ‘Results’ worksheet to include a list of Unique Players to choose from and calculate their Expected Rebounds.
To achieve this, I used the formulas shown in the YouTube video posted to Excel LADZ (up very shortly, if not already). Excel’s new array formulas in Excel 2019 and later are much more efficient. Functions like UNIQUE, FILTER and XLOOKUP do the calculations done in the video in a quarter of the time & space! If you have access to these functions, I recommend using the Normal Version of the Excel LADZ NBA Rebound Model.
Calculating Expected Rebounds
Now that I’m able to select a player, I need a formula to come up with an Expected Number of Rebounds. This has to take in the Average Rebounds for that player, and then make adjustments to that number dependent on the Quality & PACE of their opposition.
I’ll also need to adjust the Rebound Formula to factor in the amount of minutes the player is expected to play. For example, if Anthony Davis averages 12 rebounds when he plays 36 minutes, then a 50% reduction in Expected Rebounds will occur if he is expected to play 18 minutes.
Furthermore, I’ll add in a 1% home advantage. If a player is at home, their Rebounds will be increased by the square root of 1.01. On the other hand, an away game will decrease his Rebounds by the square root of 1.01. This creates a total Rebound Advantage of 1% playing at home.
Below are the formulas to calculate a player’s Offensive & Defensive Rebounds:
Offensive Rebounds = Average Offensive Rebounds * Home Advantage * % of Game Expected To Play * PACE Adjustment * Offensive Rebound Adjustment
Defensive Rebounds = Average Defensive Rebounds * Home Advantage * % of Game Expected To Play * PACE Adjustment * Defensive Rebound Adjustment
The PACE Adjustment takes into account the Average Possessions Per Game of the player’s team and his opposition. If you multiply these two figures, then take the square root, you’ll be able to grab the Expected Number of Possessions for the match. Dividing this by the Average Possessions Per Game of the player’s team, then the PACE Adjustment can be obtained.
For example, consider Anthony Davis playing the Detroit Pistons. Imagine the Lakers have a PACE of 110, with the Pistons having a PACE of 100. This yields an expected PACE of 104.88 in this game. As a multiple of the Lakers’ PACE, the PACE adjustment comes to a figure of 0.953. This multiple, below 1, reduces the Expected Offensive & Defensive Rebounds for Davis as there are less possessions expected in this match relative to the average Lakers game.
The Offensive Rebound Adjustment is a multiple which takes into account the Defensive Rebound strength of the player’s opposition. The stronger the opposition is at Defensive Rebounds, the less Offensive Rebounds that player is expected to receive. This is the same concept for the Defensive Rebound Adjustment. The stronger an opposition’s Offensive Rebounding Strength, the less Defensive Rebounds a player is expected to score.
The Offensive Rebound Adjustment is calculated by considering the Defensive Rebound % of the player's opponent. Subtract 1 from this figure to grab the Opponent's Offensive Rebound % Conceded. Then, divide by the league average Offensive Rebound %.
Consider Anthony Davis vs the Detroit Pistons again. Currently, the Pistons have a Defensive Rebound % of 76.5%. This means that they Concede an Offensive Rebound % of 23.5% (1 - 76.5%). This figure is 0.962 (96%) of the league average Offensive Rebound % of 24.42%. This 0.962 figure illustrates that the Opposition to the Pistons are relatively poor at Offensive Rebounding. As a result, this should negatively impact the Offensive Rebounds of Anthony Davis. 0.962 is the multiple applied in the Offensive Rebound formula, and serves to reduce the Expected Rebounds of Anthony Davis.
The same concept can be applied for the Defensive Rebound Adjustment in the Defensive Rebound formula.
Distributing the Expected Rebounds
By following the two formulas above, an Expected Offensive Rebounds & Expected Defensive Rebounds can be found for your player. The sum of these is equal to the Total Expected Rebounds. Your dashboard should look something like this:
Now, it’s possible to use a player’s Expected Rebounds to calculate the probability of them scoring over/under a certain number of rebounds. This can be seen below:
To do this, we can use the POISSON.DIST function in Excel. By using the Poisson Distribution from 0-8, Excel calculates the probability of Adebayo scoring 0, 1, 2, … , 8 rebounds when 9.12 is the mean (Expected Number). The sum of these probabilities is 44.04%, which is the probability of Adebayo scoring under 8.5 rebounds, considering his Expected Rebounds is 9.12. The formula is:
= POISSON.DIST(8.5, 9.12, TRUE)
The probability of Adebayo scoring over this amount is 1 - ‘Under’ = 55.96%. To calculate the decimal odds, divide 1 by the percentage chance.
Thanks for reading, DOWNLOAD the Excel LADZ NBA Rebound Model and become an Official Lad by joining the greatest Excel sports modelling community!
Download: https://www.patreon.com/posts/excel-ladz-nba-98513859
Respectively, below are the links to the data I used for the 4 worksheets of statistics.
- https://www.basketball-reference.com/leagues/NBA_2024_per_game.html
- https://www.teamrankings.com/nba/stat/possessions-per-game
- https://www.teamrankings.com/nba/stat/offensive-rebounding-pct
- https://www.teamrankings.com/nba/stat/defensive-rebounding-pct
Comments
Ike
Thanks lad I definitely plan to join tomorrow! With the NBA season slowing down, do you think you plan to add individual pitcher stats into your MLB model for further accuracy? Looking forward to joining the group soon.
Excel LADZ
G'day lad 👋 Most of the Excel files on the Patreon has the data automatically imported 🔥 This includes the NBA Model, NHL Model and the MLB Model. These models are connected to the internet through a Power Query. All you would have to do is go into the 'Data' tab of Excel, and select "Refresh All". This updates the model's connection with the internet, so that the model runs off of live stats 💪 This works for the whole season. At the start of a new season, I replace the Power Query connection to link to the new season's stats (e.g. replacing a 2023 link with a 2024 link). For example, I will be doing this for the MLB model within the next week 😃 I highly recommend you join so that you can download the models, and join the community on Discord 📈
Ike
For your models, if I join and download the excel files will all the data automatically import, or do we have to import all the statistics for the model to work. Thanks for all your work!
