Any probability experts out there?

[JenniferMurphy]

Can anyone help me with the formula for calculating the expected probability of a team winning a series of games given their history of winning home games and away games and depending on whether they have home court advantage or not?

Here's a sample minisheet. I'd like a formula for the last 2 columns. Col F would be the probability of winning a 7 game series with home court advantage. Col G would be the odds of winning that series without home court advantage.

Odds.xlsm
	C	D	E	F	G
4	Home Win%	Away Win%	Series	P(win) with	P(win) without
5	50%	50%	2-2-1-1-1
6	60%	40%	2-2-1-1-1
7	70%	30%	2-2-1-1-1
8	80%	20%	2-2-1-1-1
Sheet1

I think I know how to calculate the odds of a series of N games with fixed odds using the binomial distribution, but this is more complicated. If the 7 game series is 2-2-1-1-1 (home and away), do I need to add up a bunch of different odds:

• Win 2 at home, 2 away
• Win 2 at home, 1 of 2 away, 1 at home
• Win 2 at home, 0 away, 1 at home, 1 away,
• . . . . .

Or is there an easier way. If it's the above, I'll write a macro.

Thanks

 

[ceranes]

To calculate the expected probability of a team winning a series of games given their history of winning home games and away games and depending on whether they have home court advantage or not, you can use the following formulas for the 2-2-1-1-1 format.

For P(win) with home court advantage (Col F): P(win) = P(WWLLLL) + P(WLWLWL) + P(WLLWWL) + P(WLWLLW) + P(WLLWLW) + P(WLLWLL)

For P(win) without home court advantage (Col G): P(win) = P(LLWWLL) + P(LWLWLL) + P(LLWLWL) + P(LWLLWL) + P(LWLLWL) + P(LLWLWL)

For each term in the above formulas, you need to multiply the probabilities of winning or losing each game. For example, P(WWLLLL) = P(WH1) * P(WH2) * P(LA1) * P(LA2) * P(LH3) * P(LA3), where P(WH1) is the probability of winning the first home game, P(WH2) is the probability of winning the second home game, and so on.

For the sample mini-sheet:

For P(win) with home-court advantage (Col F):

=WH1^2 * LA1^4 + WH1 * LH1 * LA1 * LA2 * WH2 * LA3 + WH1 * LH1 * LA1 * LA2 * LH3 * LA3 + WH1 * LH1 * LA1 * LH2 * WH2 * LA3 + WH1 * LH1 * LA1 * LH2 * LH3 * LA3 + WH1 * LH1 * LH2 * LA2 * WH2 * LA3

For P(win) without home-court advantage (Col G):

=LA1^4 * LH1^2 + LA1 * LH1 * WH1 * LH2 * LA2 * LH3 + LA1 * LH1 * WH1 * LH2 * LH3 * LA3 + LA1 * LH1 * WH1 * LA2 * LH2 * LH3 + LA1 * LH1 * WH1 * LA2 * LH2 * LA3 + LA1 * LH1 * LA2 * LH2 * WH1 * LH3

Replace WH1 with the home win% (e.g., 0.5 for 50%) and replace LA1 with 1 - away win% (e.g., 0.5 for 50%). LH1 and LH2 are the losing probabilities for home games, and LH3 is the losing probability for the final home game. LH1 = 1 - WH1, LH2 = 1 - WH2, and LH3 = 1 - WH2 (since the final home game is also played at the same venue as the second home game). Replace LA2 and LA3 with the losing probabilities for away games. LA2 = 1 - WH1 and LA3 = 1 - WH2 (since the final away game is also played at the same venue as the second away game).


Hope this helps!
Chris

 

[JenniferMurphy]

To calculate the expected probability of a team winning a series of games given their history of winning home games and away games and depending on whether they have home court advantage or not, you can use the following formulas for the 2-2-1-1-1 format.

For P(win) with home court advantage (Col F): P(win) = P(WWLLLL) + P(WLWLWL) + P(WLLWWL) + P(WLWLLW) + P(WLLWLW) + P(WLLWLL)

For P(win) without home court advantage (Col G): P(win) = P(LLWWLL) + P(LWLWLL) + P(LLWLWL) + P(LWLLWL) + P(LWLLWL) + P(LLWLWL)

Unless there is something I don't understand, I think you are missing a few combinations.

I have made some progress on this. The Combin function tells us that there are 35 combinations of 7 items taken 4 at a time. That is, there are 35 ways for my team to win 4 games and lose 3 if we always play all 7 games even if one team wins 4 earlier. There are also 35 combinations of 7 items taken 3 at a time. That is, there are also 35 ways for my team to win 3 games and lose 4, again, playing all 7 games. This is always true of the middle 2 combinations of any odd number of items in the binomial expansion, because Pascal's Triangle is symmetric.

Odds Series Home & Away.xlsm
	B	C	D
5	Games	Wins	Comb
6	7	4	35
7	7	3	35
Calc Paths

Cell Formulas
Range	Formula
D6:D7	D6	=COMBIN([@Games],[@Wins])

But that is misleading. Some of those ways, such as WWWWLLL can never happen because the series would be over after 4 games. We need to add up all of the ways for my team to get 3 wins before the other team gets 4. Then, since no one has won yet, all of the combinations are valid. Then we add that final win to win the series. That's all of the combinations of winning 4-0, 4-1, 4-2, & 4-3. We do this by adding up 4 sets of combinations to get to 3-0, 3-1, 3-2, and 3-3.

Odds Series Home & Away.xlsm
	F	G	H
5	Games	Wins	Comb
6	3	3	1
7	4	3	4
8	5	3	10
9	6	3	20
10	Total		35
Calc Paths

Cell Formulas
Range	Formula
H6:H9	H6	=COMBIN([@Games],[@Wins])
H10	H10	=SUBTOTAL(109,[Comb])

There is 1 way to get to 3-0, 4 ways for 3-1, 10 ways for 3-2, and 20 ways for 3-3. Those add up to 35. So there are 35 ways for my team to win a 7-game series.

To get the odds, we calculate the odds more or less as you did for 3-0 to 3-3 and then add that last win.

I think I have a way to do that with a recursive subroutine. Here are the combinations that a first draft of it generated. This is for all 70 combinations with either team winning.

I'll post the probabilities as soon as I get it working.

Comments?

 

[ceranes]

My apologies for not providing all the possible combinations initially. You are correct that there are more combinations to consider, and your approach to finding them is a good one. Based on your progress, we can find the probability of winning a 7-game series using the binomial distribution and taking into account all possible combinations.
Here's a summary of the probabilities to consider for each combination:

1. Win 4-0: P(WWLL) = (H^3 * A)
2. Win 4-1: P(WWLWL) + P(WWLLW) + P(WLWLW) = (H^2 * A^2) * (3)
3. Win 4-2: P(WWLWLL) + P(WWLLWL) + P(WWLLLW) + P(WLWLWL) + P(WLWLLW) + P(WLLWLW) = (H * A^3) * (6)
4. Win 4-3: P(WLLWLLW) + P(WLWLLWL) + P(WLWLWLL) + P(WLLWLWL) + P(WLLWLLW) + P(WLWLWLW) = (H^4) * (6)

Add these probabilities to find the total probability of winning a 7-game series:
P(win) = P(WWLL) + P(WWLWL) + P(WWLLW) + P(WLWLW) + P(WWLWLL) + P(WWLLWL) + P(WWLLLW) + P(WLWLWL) + P(WLWLLW) + P(WLLWLW) + P(WLLWLLW) + P(WLWLLWL) + P(WLWLWLL) + P(WLLWLWL) + P(WLLWLLW) + P(WLWLWLW)

If you have home court advantage:
P(win) = H^3 * A + 3 * H^2 * A^2 + 6 * H * A^3 + 6 * H^4

If you don't have home court advantage:
P(win) = A^3 * H + 3 * A^2 * H^2 + 6 * A * H^3 + 6 * A^4

Good luck with your recursive subroutine.

 

[JenniferMurphy]

My apologies for not providing all the possible combinations initially. You are correct that there are more combinations to consider, and your approach to finding them is a good one. Based on your progress, we can find the probability of winning a 7-game series using the binomial distribution and taking into account all possible combinations.
Here's a summary of the probabilities to consider for each combination:

1. Win 4-0: P(WWLL) = (H^3 * A)
2. Win 4-1: P(WWLWL) + P(WWLLW) + P(WLWLW) = (H^2 * A^2) * (3)
3. Win 4-2: P(WWLWLL) + P(WWLLWL) + P(WWLLLW) + P(WLWLWL) + P(WLWLLW) + P(WLLWLW) = (H * A^3) * (6)
4. Win 4-3: P(WLLWLLW) + P(WLWLLWL) + P(WLWLWLL) + P(WLLWLWL) + P(WLLWLLW) + P(WLWLWLW) = (H^4) * (6)

Add these probabilities to find the total probability of winning a 7-game series:
P(win) = P(WWLL) + P(WWLWL) + P(WWLLW) + P(WLWLW) + P(WWLWLL) + P(WWLLWL) + P(WWLLLW) + P(WLWLWL) + P(WLWLLW) + P(WLLWLW) + P(WLLWLLW) + P(WLWLLWL) + P(WLWLWLL) + P(WLLWLWL) + P(WLLWLLW) + P(WLWLWLW)

If you have home court advantage:
P(win) = H^3 * A + 3 * H^2 * A^2 + 6 * H * A^3 + 6 * H^4

If you don't have home court advantage:
P(win) = A^3 * H + 3 * A^2 * H^2 + 6 * A * H^3 + 6 * A^4

Good luck with your recursive subroutine.

Again, unless there is something I don't understand about your notation, none of these probabilities are correct.

Let Ph = probability of winning at home & Pa = probability of winning away (on the road). And assume a 2-2-1-1-1 series format.

If my team wins 4-0, that's not P(WWLL), assuming that W=Win & L=Lose.

The correct expression with home court is Ph*Ph*Pa*Pa and without home court it's Pa*Pa*Ph*Ph. In this one case, they have the same result, but calculated differently.

None of the others are so simple. For the winning 4-1 case, there a 4 possible combinations with these odds with home court:

WWWLW = Ph*Ph*Pa*(1-Pa)*Ph
WWLWW = Ph*Ph*(1-Pa)*Pa*Ph
WLWWW = Ph*(1-Ph)*Pa*Pa*Ph
LWWWW = (1-Ph)*Ph*Pa*Pa*Ph

The other combinations are more complicated. I don't know of a closed form expression for the sum of them all.

 

[Gokhan Aycan]

Those responses look very ChatGPT-like "My apologies for not providing all the possible combinations initially. You are correct that ..."

 

[RoryA]

Those responses look very ChatGPT-like

Yes they do. If that is the case @ceranes please note Message Board Rules

 

[JenniferMurphy]

Those responses look very ChatGPT-like "My apologies for not providing all the possible combinations initially. You are correct that ..."

Wow. That never occurred to me. Just what we need -- one more source of disinformation (sigh). Thanks for pointing that out.

 

[Dan_W]

The "Good luck with your recursive subroutine" part also seems like a bit of a dead giveaway.

 

[ceranes]

Look, I'm not going to debate this thing with everyone, but I wanted to address Jennifer directly. You can mock me or call it disinformation, but in all reality, as I mentioned to @RoryA, we have solved some pretty big problems at work using ChatGPT. Problems would've taken weeks of research, but we solved them in just a matter of a few days. I understand if you're not familiar with it but think of it as Google. If you have a problem, you can search Google to your heart's content, but you may or may not find the answer you're looking for. ChatGPT has already learned everything on the Internet, so all you need to do is ask the right questions, and you will get the answer you want. The problem here is we hadn't fine-tuned the questions yet before everyone started. My sole desire was to try and help someone solve a problem that had a large number of views and no responses. I understand how frustrating it can be to come to a forum like this, and not get any kind of help. Personally, if I don't get an answer, or get one that works, I keep searching until I find one that does work. I don't think anything of the person who didn't get it just right.

So, Jennifer, I apologize to you. My intention was to not "mislead" you, I was simply trying to help.

As @RoryA pointed, out, a new rule was added pertaining to the use of ChatGPT. Since I came on board in 2018, and just recently started back in myself, I was not aware of the new rule. So, moving forward, I will no longer be using the service to aid people here.