Probability after Loss in a row

[jamesmoreno]

Hey guys,

I have a spreadsheet with the past frequency of losses in a row before having a winner. What formula can I use to calculate the probability of having a winner after 1,2,3(etc) losses in a row according do this data?

Thanks, appreciate the help!

 

[StephenCrump]

It will be easy enough to analyse your data to see what the experience has been, e.g. for every sequence that starts LL..., how many LLL... were there? And how many LLW....

If the individual results are independent and with the same probability p of a win (your data may not be!) then the probability of an individual W will be p, regardless of what's happened previously. In simple terms, for the 11th fair-coin flip after 10 successive heads, the expectation is 50% heads, 50% tails.

 

[jamesmoreno]

It will be easy enough to analyse your data to see what the experience has been, e.g. for every sequence that starts LL..., how many LLL... were there? And how many LLW....

If the individual results are independent and with the same probability p of a win (your data may not be!) then the probability of an individual W will be p, regardless of what's happened previously. In simple terms, for the 11th fair-coin flip after 10 successive heads, the expectation is 50% heads, 50% tails.

Can you explain me better the first part of your reply? What do you mean with LL, LLL, LLW, and how can I do that?

As I showed on the second image, in that case, the probability of having a winner after a loss is 100%, I don't know what the formula to calculate that or what process to achieve that.

I understand that concept. That's like martingale in roulette for example. Even if RED comes up 40 times, the probability of coming up 41 times, is always 50/50, no matter the 40 previous results.

Thanks again for taking the time

 

[StephenCrump]

I've used RAND() to generate some dummy data. You could use real data.

There are 13 loss runs (highlighted), seven of length 1. Therefore 7/13 = 54% were Loss,Win... (i.e. rather than Loss,Loss....)

Similarly, there are three loss runs of length 4 or more, and one with length 4, so the probability of Loss,Loss,Loss,Loss,Win... (i.e rather than Loss,Loss,Loss,Loss,Loss...) is 1/3 = 33%.

	A	B	C	D	E	F
1				Last win	49
2	p	30%				Prob. Win
3				Length		after
4	Try	Result		of run (N)	N losses	N losses
5	1	L		1	7	54%
6	2	L		2	2	33%
7	3	W		3	1	25%
8	4	L		4	1	33%
9	5	L		5	0	0%
10	6	L		6	1	50%
11	7	L		7	0	0%
12	8	W		8	1	100%
13	9	L		9	0
14	10	W		10	0
15	11	L		11	0
16	12	L		12	0
17	13	L		13	0
18	14	L		14	0
19	15	L		15	0
20	16	L
21	17	W
22	18	L
23	19	W
24	20	L
25	21	L
26	22	W
27	23	L
28	24	W
29	25	L
30	26	L
31	27	L
32	28	W
33	29	W
34	30	L
35	31	W
36	32	L
37	33	W
38	34	L
39	35	L
40	36	L
41	37	L
42	38	L
43	39	L
44	40	L
45	41	L
46	42	W
47	43	W
48	44	W
49	45	L
50	46	W
51	47	L
52	48	W
53	49	W
54	50	L
Sheet1

Cell Formulas
Range	Formula
E1	E1	=LOOKUP(2,1/(Results="W"),A5:A25)
F5:F18	F5	=IFERROR(E5/SUM(E5:E$19),"")
E5:E19	E5	=IFERROR(SUMPRODUCT(--(FREQUENCY(IF(ResultsToUse="L", ROW(ResultsToUse)), IF(ResultsToUse="W", ROW(ResultsToUse)))=$D5)),0)
B5:B54	B5	=IF(RAND()<p,"W","L")

Named Ranges
Name	Refers To	Cells
LastWin	=Sheet1!$E$1	E5:E19
p	=Sheet1!$B$2	B5:B54
Results	=Sheet1!$B$5:$B$54	E1, E5:E19
ResultsToUse	=INDEX(Results,1):INDEX(Results,LastWin)	E1, E5:E19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B54	Expression	=AND(B5="L",ROWS(B$5:B5)<LastWin)	text	NO