Excel LADZ NBA Model | Overtime & Live Win Probability

G’day, lads

In this video I ran through 3 major changes to the NBA Model. I’ll summarise how I did them below.

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

xFGA+FTA Change

In the previous formula to simulate a player’s points, xFGA+FTA was kept constant. That is, the simulated amount of 3PT’s, 2PT’s and FT attempts always combined to be equal to the player’s xFGA+FTA. However, this shouldn’t be the case as a player’s usage in a game often fluctuates.

As a result, F12 (xFGA+FTA for the starting PG) is now simulated using the formula: BINOM.INV(10000, F12/10000, RAND()).

The BINOM.INV function runs 10,000 trials, with the probability of success for each trial being the xFGA+FTA (F12) divided by 10,000 (number of trials). The RAND() function then simulates a random number of FGA+FTA for the player based on the inverse of the binomial cumulative distribution function.

Overtime Change

If a game is tied after 48 minutes of Normal Time, 5 minutes of Overtime is played. This process continues until a winner is established at the end of a period. In this model, I included a maximum of 3 Overtime periods (Columns O - Q).

Overtime can be thought of as a game in itself; just 5/48 the length. As a result, simulating a player’s points in Overtime should use the same formula. However, you must scale down a player’s xFGA+FTA to 5/48 of the Normal Time formula. This can be captured through an IF function.

IF the sum of the Home Team Normal Time Points (Column N) is equal to the sum of the Away Team Normal Time Points, then the formula runs 5/48 of the match again through the BINOM.INV function. If not, then “ ” is returned. This means the cell is left blank and no value is recorded if the game finishes in normal time.

Live Win Probability

To capture the Expected Points and Live Win % in real time, the same concept can be applied. If 3 quarters have been played, then 75% of the match has already been decided, leaving 25% left to be played. Therefore, the Pts. Sim formula can be scaled down 75% to reflect only one quarter being left to simulate (as its outcome hasn’t been decided/played yet) in the match.

I’ve managed to capture this “scale” by inputting the current quarter in cell L3 of the ‘Live Win Probability’ worksheet as well as the quarter time left in cell L6. Then, I’ve used a combination of the VALUE, RIGHT and LEFT functions to read these cells and translate the minutes left in the game to a fraction over 48.

I decided to apply this function directly to a player’s xFGA+FTA in cell F13 in order to make the formulas more readable. Like I did in the overtime columns (O - Q), this function could also be attached to each F13 to scale down a player’s expected shots left in the match. However, this would make the formula extremely long.

As there’s only 5:00 left in the image above, Russell’s xFGA+FTA is scaled down from 14.0 (able to be seen in the Result worksheet) to only 1.5. This will automatically impact the Pts Sim. formula and will result in the simulated number of points for a team naturally decreasing as time runs out.

I also had to make a change to the Overtime cells, changing F13 to Result!F12. This is because the 5/48 change should be applied to a player’s full 48 minute xFGA+FTA figure, not the scaled one present in Column F of the ‘Live Win Probability’ worksheet.

Notes

• You may notice in the intro to the video, the teams change but the players from Rows 13 downwards do not. When recording this, I forgot to change the teams from the ‘Result’ worksheet. Please make sure that when you’re changing the matchup, change it from the ‘Result’ worksheet and NOT ‘Live Win Probability’.

Thank you for reading lads, JOIN to become a lad!