JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,691
- Office Version
- 365
- Platform
- Windows
With the NBA playoffs coming up and my Golden State Warriors, the defending champs, having a rocky year, a repeat looks highly unlikely. Overall, they are just above .500. They are .806 at home (29-7) but only .216 on the road (8-29). I decided to see if I could calculate the estimated probability of them winning a 7-game series taking into account their records at home and away.
The probability of X successes in N trials with a fixed probability is easily calculated using the binomial distribution. Here are some examples.
But the odds of 4 successes in 7 trials is not quite what we need. It includes combinations like WWWWLLL. Those last 3 games would not be played because the series ends when either team wins 4 games. What we need is the sum of 4 binomial distributions that each get one team to 3 wins without the other team winning more than 3 (3 of 3, 3 of 4, 3 of 5, & 3 of 6). Then we have to multiply each of those by the probability that my team wins the next (last) game. Here are some examples of that.
But it's more complicated than that. The probabiity of winning 1 game is not fixed. I want to take into account their odds of winning at home vs away. I couldn't come up with a formula that would do that calculation. If there is one, please let me know. I came up with two ways to do it. One way is a simulation. I can simulate the series a number of times and divide the wins by the number of simulations. This will never get an exact result. It's always an approximation. I wrote code to do the simulation. It takes at least 500,000 iterations to get stable results. I can pozst that code if anyone cares.
The other way is to generate a list of all possible combinations of winning sequences and apply the appropriate probabilities. If they win 4 straight, 2 on the road and 2 at home, the odds of that sequence where Ph = probability of a win at home and Pa = the probability of a win away is
If the winning sequence is LWLLWWW in a 2-2-1-1-1 series without home court advantage, the odds of that sequence is
Lacking an expression that would calculate these odds, I looked into doing it iteratively. This means generating all possible combinations of wins and losses. I think the way to do it is with recursion. Recursion is very powerful, but can be tricky.
It's been a very long time since my last comp sci class, so it took me quite a bit of trial and error (mostly error) and some help from folks here to get it working. The code below is, IMHO, a reasonably good example of how recursion can handle something like this. All this version of code does is generate all possible combinations of wins and losses in the designated series. It takes just one parameter: the number of games. (Later I want to add a second parameter with the series schedule, such as 2-2-1-1-1 or 2-3-2.) Next I want to have it apply the Ph & Pa probabilities and add them up.
It writes the list of combinations to the calling sheet.
A 1-game series has just 2 outcomes.
A 3-game series has 6 possible outcomes.
A 7-game series has 70 possible outcomes. Here are the first 10 and the last 10.
And here's the code. It looks for 2 named ranges: NumGames, the number of games in the series (must be an odd number) & Corner (the upper left corner for where to write the results).
The secret of recursion is local and global variables. The global variable are passed ByRef. They are constant across all iterations. All of the other variables are passed ByVal meaning that they are local to each iteration. They are added to by each iteration and passed onto the next.
This was very educational for me. I hope it is helpful to others. When I get the complete code to calculate the odds working, I'll post the results, unless this is all too much.
If anyone sees a way to improve the code, I would love to hear about it.
The probability of X successes in N trials with a fixed probability is easily calculated using the binomial distribution. Here are some examples.
| Odds Series Home & Away.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | |||
| 3 | 25% | 33% | 50% | 67% | 75% | ||||
| 4 | Wins | Games | Prob1 | Prob2 | Prob3 | Prob4 | Prob5 | ||
| 5 | 0 | 7 | 13.35% | 5.85% | 0.78% | 0.05% | 0.01% | ||
| 6 | 1 | 7 | 31.15% | 20.48% | 5.47% | 0.64% | 0.13% | ||
| 7 | 2 | 7 | 31.15% | 30.73% | 16.41% | 3.84% | 1.15% | ||
| 8 | 3 | 7 | 17.30% | 25.61% | 27.34% | 12.80% | 5.77% | ||
| 9 | 4 | 7 | 5.77% | 12.80% | 27.34% | 25.61% | 17.30% | ||
| 10 | 5 | 7 | 1.15% | 3.84% | 16.41% | 30.73% | 31.15% | ||
| 11 | 6 | 7 | 0.13% | 0.64% | 5.47% | 20.48% | 31.15% | ||
| 12 | 7 | 7 | 0.01% | 0.05% | 0.78% | 5.85% | 13.35% | ||
| 13 | Total | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | |||
Binom | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3 | E3 | =1/3 |
| G3 | G3 | =2/3 |
| D5:H12 | D5 | =BINOM.DIST([@Wins],[@Games],D$3,FALSE) |
| D13 | D13 | =SUBTOTAL(109,[Prob1]) |
| E13 | E13 | =SUBTOTAL(109,[Prob2]) |
| F13 | F13 | =SUBTOTAL(109,[Prob3]) |
| G13 | G13 | =SUBTOTAL(109,[Prob4]) |
| H13 | H13 | =SUBTOTAL(109,[Prob5]) |
But the odds of 4 successes in 7 trials is not quite what we need. It includes combinations like WWWWLLL. Those last 3 games would not be played because the series ends when either team wins 4 games. What we need is the sum of 4 binomial distributions that each get one team to 3 wins without the other team winning more than 3 (3 of 3, 3 of 4, 3 of 5, & 3 of 6). Then we have to multiply each of those by the probability that my team wins the next (last) game. Here are some examples of that.
| Odds Series Home & Away.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | |||
| 4 | 25% | 33% | 50% | 67% | 75% | ||||
| 5 | Wins | Games | P1(Series) | P2(Series) | P3(Series) | P4(Series) | P5(Series) | ||
| 6 | 4 | 4 | 0.39% | 1.23% | 6.25% | 19.75% | 31.64% | ||
| 7 | 4 | 5 | 1.17% | 3.29% | 12.50% | 26.34% | 31.64% | ||
| 8 | 4 | 6 | 2.20% | 5.49% | 15.63% | 21.95% | 19.78% | ||
| 9 | 4 | 7 | 3.30% | 7.32% | 15.63% | 14.63% | 9.89% | ||
| 10 | Total | 7.06% | 17.33% | 50.00% | 82.67% | 92.94% | |||
Series | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F4 | F4 | =1/3 |
| H4 | H4 | =2/3 |
| E6:I9 | E6 | =BINOM.DIST([@Wins]-1,[@Games]-1,E$4,FALSE) * E$4 |
| E10 | E10 | =SUBTOTAL(109,[P1(Series)]) |
| F10 | F10 | =SUBTOTAL(109,[P2(Series)]) |
| G10 | G10 | =SUBTOTAL(109,[P3(Series)]) |
| H10 | H10 | =SUBTOTAL(109,[P4(Series)]) |
| I10 | I10 | =SUBTOTAL(109,[P5(Series)]) |
But it's more complicated than that. The probabiity of winning 1 game is not fixed. I want to take into account their odds of winning at home vs away. I couldn't come up with a formula that would do that calculation. If there is one, please let me know. I came up with two ways to do it. One way is a simulation. I can simulate the series a number of times and divide the wins by the number of simulations. This will never get an exact result. It's always an approximation. I wrote code to do the simulation. It takes at least 500,000 iterations to get stable results. I can pozst that code if anyone cares.
The other way is to generate a list of all possible combinations of winning sequences and apply the appropriate probabilities. If they win 4 straight, 2 on the road and 2 at home, the odds of that sequence where Ph = probability of a win at home and Pa = the probability of a win away is
Code:
Pa x Pa x Ph x PhIf the winning sequence is LWLLWWW in a 2-2-1-1-1 series without home court advantage, the odds of that sequence is
Code:
(1-Pa) x Pa x (1-Ph) x (1-Ph) x Pa x Ph x PaLacking an expression that would calculate these odds, I looked into doing it iteratively. This means generating all possible combinations of wins and losses. I think the way to do it is with recursion. Recursion is very powerful, but can be tricky.
It's been a very long time since my last comp sci class, so it took me quite a bit of trial and error (mostly error) and some help from folks here to get it working. The code below is, IMHO, a reasonably good example of how recursion can handle something like this. All this version of code does is generate all possible combinations of wins and losses in the designated series. It takes just one parameter: the number of games. (Later I want to add a second parameter with the series schedule, such as 2-2-1-1-1 or 2-3-2.) Next I want to have it apply the Ph & Pa probabilities and add them up.
It writes the list of combinations to the calling sheet.
A 1-game series has just 2 outcomes.
A 3-game series has 6 possible outcomes.
A 7-game series has 70 possible outcomes. Here are the first 10 and the last 10.
And here's the code. It looks for 2 named ranges: NumGames, the number of games in the series (must be an odd number) & Corner (the upper left corner for where to write the results).
The secret of recursion is local and global variables. The global variable are passed ByRef. They are constant across all iterations. All of the other variables are passed ByVal meaning that they are local to each iteration. They are added to by each iteration and passed onto the next.
VBA Code:
'======================================================================================
' Generate all possible Win/Loss combinations recursively
' This code generates all possible winning strings in a series of NumGames.
' This is just to test that the recursion code work.
' Change Log
' 03/19/23 Created & tested
' 03/20/23 Split off from the simulation code.
' 03/21/23 Now works for all NumGames
'======================================================================================
Sub WinLossCombs()
Const MyName As String = "WinLossCombs" 'Name lof routine for messages
Const rnNumGames As String = "NumGames" 'Name of number-of-games cell
Const rnCorner As String = "Corner" 'Name of upper right corner of table
Dim NumGames As Long 'Number of games
NumGames = Range(rnNumGames).Value2 'Load the number of games
If (NumGames < 1) _
Or (1 <> (NumGames Mod 2)) _
Or (NumGames <> Int(NumGames)) Then 'Is it valid?
MsgBox "NumGames (" & NumGames & ") is not a positive odd number" _
, vbOKOnly, MyName
Exit Sub: End If
Dim MaxWins As Long 'Number of wins to win the series
MaxWins = Int(NumGames / 2) + 1 '= half of the games rounded up
Const WinChar As Variant = "W" 'The "win" character
Const LssChar As Variant = "L" 'The "loss" character
' Write the series header
Dim i As Long 'Loop index
With Range(rnCorner)
.Value2 = "Series" 'Write series header
.HorizontalAlignment = xlCenter 'Center it
.Font.Bold = True 'Make it bold
With .Offset(0, 1)
.Value2 = "W-L" 'W-L header 'Write W-L header
.HorizontalAlignment = xlCenter 'Center it
.Font.Bold = True 'Make it bold
End With
End With
For i = 1 To NumGames 'Loop thru the game numbers
With Range(rnCorner).Offset(0, i + 1) 'Select the range
.Value2 = i 'Write the game number
.HorizontalAlignment = xlCenter 'Center it
.Font.Bold = True 'Make it bold
End With
Next i
Dim Series As String 'The actual series: WWWW WWWLW WWLWW etc.
Series = "" 'Start will no wins or losses
Dim iSeries As Long 'The series number (1 to whatever)
iSeries = 1 'Start with series #1
Dim NumWins As Long 'Number of wins so far
NumWins = 0 'Start with zero wins
Dim NumLsss As Long 'Number of losses so far
NumLsss = 0 'Start with zero losses
' Call the Parent code
Call WinLossParent(iSeries, MaxWins, NumGames, NumWins, NumLsss, Series, rnCorner)
End Sub
'======================================================================================
' Parent code
' At each point in any series, this code starts a Win/Loss fork.
' It is only called if the active series is not finished.
' It simply calls the child code twice. Once to add a win and once to add a loss.
'======================================================================================
Sub WinLossParent(ByRef iSeries, ByRef MaxWins, ByRef NumGames _
, ByVal NumWins As Long, ByVal NumLsss As Long _
, ByVal Series As String _
, ByRef rnCorner)
Call WinLossChild("Win", iSeries, MaxWins, NumGames, NumWins, NumLsss, Series, rnCorner)
Call WinLossChild("Loss", iSeries, MaxWins, NumGames, NumWins, NumLsss, Series, rnCorner)
End Sub
'======================================================================================
' Child code
' This code will add a win or a loss to the active series.
' If that ends the series, it logs it and exits.
' If not, it calls the Parent code to do a new fork.
'======================================================================================
Sub WinLossChild(ByVal pWinLoss As String _
, ByRef iSeries, ByRef MaxWins, ByRef NumGames _
, ByVal NumWins As Long, ByVal NumLsss As Long _
, ByVal Series As String _
, ByRef rnCorner)
Select Case UCase(pWinLoss)
Case "WIN" 'Add a win
NumWins = NumWins + 1 'Bump the count
Series = Series & "W" 'Add a win
If NumWins = MaxWins Then 'If we have a winner, log it
Call Put2Table(rnCorner, NumGames, iSeries, Series, NumWins, NumLsss) 'Add to table
iSeries = iSeries + 1 'Start the next series
Else 'If no winner, call the Parent to to a fork
Call WinLossParent(iSeries, MaxWins, NumGames, NumWins, NumLsss, Series, rnCorner)
End If
Case "LOSS" 'Add a loss
NumLsss = NumLsss + 1 'Bump the count
Series = Series & "L" 'Add a loss
If NumLsss = MaxWins Then 'If we have a winner, log it
Call Put2Table(rnCorner, NumGames, iSeries, Series, NumWins, NumLsss) 'Add to table
iSeries = iSeries + 1 'Start the next series
Else 'If no winner, call the Parent to to a fork
Call WinLossParent(iSeries, MaxWins, NumGames, NumWins, NumLsss, Series, rnCorner)
End If
End Select
End Sub
'======================================================================================
' Write the Series to the Table
'======================================================================================
Sub Put2Table(ByRef rnCorner, ByRef NumGames _
, ByVal iSeries As Long, ByVal Series As String _
, ByVal NumWins As Long, ByVal NumLsss As Long)
Dim i As Long 'Loop index
Series = Series & String(NumGames, " ") 'Pad the series
With Range(rnCorner).Offset(iSeries, 0)
.Value2 = iSeries 'Write the series number
.HorizontalAlignment = xlCenter 'Center it
End With
With Range(rnCorner).Offset(iSeries, 1)
.Value2 = "'" & NumWins & "-" & NumLsss 'Write the W-L result
.HorizontalAlignment = xlCenter 'Center it
End With
For i = 1 To NumGames 'Write the Ws & Ls
With Range(rnCorner).Offset(iSeries, i + 1)
.Value2 = Mid(Series, i, 1) 'Write a W or L
.HorizontalAlignment = xlCenter 'Center it
End With
Next i
End SubThis was very educational for me. I hope it is helpful to others. When I get the complete code to calculate the odds working, I'll post the results, unless this is all too much.
If anyone sees a way to improve the code, I would love to hear about it.
