Probability of getting to N wins first

[JenniferMurphy]

Suppose I want to know the odds that my team will get to N wins before its opponent, assuming a constant probability, P, that it will win any 1 game.

This involves the sum of N probabilities since the total number of games varies from 1 to 2*N - 1.

I can do it using COMBIN, but it requires a sum of N terms:

I fiddled around with the various binomial distribution functions, but couldn't find one that will do this with a single expression. They all require a sum of N terms, which gets unwieldy for N>5.

I could write a UDF to do the sum in a loop, but before I do, is there a way do do this with a single expression for all N?

Thanks

 

[JGordon11]

Book1
	A	B	C	D	E	F	G
1
2			N	5		0.733432
3			P	0.6
4
5
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(COMBIN(N*2-1,ROW(INDIRECT("1:" & N))-1),0.6^(2*N-ROW(INDIRECT("1:" & N))),(1-P)^(ROW(INDIRECT("1:" & N))-1))

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$D$2	F2
P	=Sheet1!$D$3	F2

 

[JGordon11]

Oops, left a 0.6 rather than P in the previous post. Use:

Book1
	A	B	C	D	E	F	G
1
2			N	5		0.733432
3			P	0.6
4
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(COMBIN(N*2-1,ROW(INDIRECT("1:" & N))-1),P^(2*N-ROW(INDIRECT("1:" & N))),(1-P)^(ROW(INDIRECT("1:" & N))-1))

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$D$2	F2
P	=Sheet1!$D$3	F2

 

[JenniferMurphy]

Oops, left a 0.6 rather than P in the previous post.

Wow! My hat's off to you!!!

I think it would take me a week to decipher that and zero chances of retyping it correctly. I'll just trust you that it works.

For me, a UDF solution works better. Here's my code:

Function Odds(P As Double, N As Integer) As Double

Dim i As Integer
Dim Q As Double
Q = 1 - P

Odds = 0
For i = 0 To N - 1
  Odds = Odds + Application.WorksheetFunction.Combin(i + N - 1, i) * Q ^ i
Next i

Odds = P ^ N * Odds

End Function

And these are the results:

 

[JGordon11]

Thanks - UDF is easier to understand and isn't volatile like the formula is (due to use of INDIRECT). But the formula will give the same results in cases where a UDF is not desired. Also it could be turned into lambda function such as odds(n,p) which would make its usage much simpler when/if lambda functions become generally available.

Book1
	A	B	C	D	E
1	Wins	Probability		P	0.6
2	1	60.0%
3	2	64.8%
4	3	68.3%
5	4	71.0%
6	5	73.3%
7	10	81.4%
8	15	86.4%
9	20	89.8%
10	25	92.2%
11	50	97.8%
12	100	99.8%
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=SUMPRODUCT(COMBIN(A2*2-1,ROW(INDIRECT("1:" & A2))-1),P^(2*A2-ROW(INDIRECT("1:" & A2))),(1-P)^(ROW(INDIRECT("1:" &A2))-1))

Named Ranges
Name	Refers To	Cells
P	=Sheet1!$E$1	B2:B12