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!

 

[shg]

If the events are independent (like tossing a coin, rolling a die, or winning a lottery) the history does not predict the future.

 

[jamesmoreno]

If the events are independent (like tossing a coin, rolling a die, or winning a lottery) the history does not predict the future.

Thanks for your reply. I know in that case of coin toss, it's independent, and you can encounter 30 losses in a row or more, even though in history it never happen.
But according to past data, I wanted to know what is the probability of said event, even if it's not accurate prediction. Can you help?

 

[JGordon11]

Book2
	A	B	C
2	487	1	80.9%
3	89	2	14.8%
4	21	3	3.5%
5	3	4	0.5%
6	2	5	0.3%
7	0	6	0.0%
8	0	7	0.0%
9	0	8	0.0%
Sheet4

Cell Formulas
Range	Formula
C2:C9	C2	=A2/SUM($A$2:$A$9)

 

[StephenCrump]

Welcome to the Forum!

You've left out the count for zero previous losses.

If these are independent trials with equal probability of success, what you've posted (so far) is consistent with the distribution you'd expect for individual success rate ~80%

	A	B	C	D	E	F
1	p	80%
2
3	Previous
4	losses	Expected			Actual
5	0	80.0%			???
6	1	16.0%		80.0%	487	80.9%
7	2	3.2%		16.0%	89	14.8%
8	3	0.6%		3.2%	21	3.5%
9	4	0.1%		0.6%	3	0.5%
10	5	0.0%		0.1%	2	0.3%
11	6	0.0%		0.0%	0	0.0%
12	7	0.0%		0.0%	0	0.0%
13	8	0.0%		0.0%	0	0.0%
14	9	0.0%
15	10	0.0%
Sheet1

Cell Formulas
Range	Formula
D6:D13	D6	=B6/SUM(B$6:B$13)
F6:F13	F6	=E6/SUM(E$6:E$13)
B5	B5	=p
B6:B15	B6	=p*(1-p)^A6

Named Ranges
Name	Refers To	Cells
p	=Sheet1!$B$1	B5:B15

 

[jamesmoreno]

Book2
	A	B	C
2	487	1	80.9%
3	89	2	14.8%
4	21	3	3.5%
5	3	4	0.5%
6	2	5	0.3%
7	0	6	0.0%
8	0	7	0.0%
9	0	8	0.0%
Sheet4

Cell Formulas
Range	Formula
C2:C9	C2	=A2/SUM($A$2:$A$9)

Hey, thanks for your reply, but that just tells the distribution %, which is not what I am looking for...

 

[StephenCrump]

If the distribution in #5 is correct (i.e. if p is 80%, and trials are independent) then in an unbounded sample space, you expect
- 80% of wins to follow an immediately previous win
- 16% of wins to follow a single loss
- 3.2% of wins to follow a run of two losses
etc.

I suspect you're still looking for an answer to your original question, which might be phrased: I've experienced N losses in a row. What is my chance of a win next time?

The answer to that question is p, regardless of the value of N (as @shg said in post #2).

 

[jamesmoreno]

Welcome to the Forum!

You've left out the count for zero previous losses.

If these are independent trials with equal probability of success, what you've posted (so far) is consistent with the distribution you'd expect for individual success rate ~80%

	A	B	C	D	E	F
1	p	80%
2
3	Previous
4	losses	Expected			Actual
5	0	80.0%			???
6	1	16.0%		80.0%	487	80.9%
7	2	3.2%		16.0%	89	14.8%
8	3	0.6%		3.2%	21	3.5%
9	4	0.1%		0.6%	3	0.5%
10	5	0.0%		0.1%	2	0.3%
11	6	0.0%		0.0%	0	0.0%
12	7	0.0%		0.0%	0	0.0%
13	8	0.0%		0.0%	0	0.0%
14	9	0.0%
15	10	0.0%
Sheet1

Cell Formulas
Range	Formula
D6:D13	D6	=B6/SUM(B$6:B$13)
F6:F13	F6	=E6/SUM(E$6:E$13)
B5	B5	=p
B6:B15	B6	=p*(1-p)^A6

Named Ranges
Name	Refers To	Cells
p	=Sheet1!$B$1	B5:B15

Hey, thanks for your reply! I left out the count for zero previous losses, because I have them in a separate table. I have one table for Loss in a row frequency, and another for Wins in a row frequency, I updated the spreadsheet image.

What I'm looking to do, is according to this data, see what is the probability of having a winner after 1 loss in a row (one iteration fails, before the next one wins), or 2 losses in a row (2 iterations fail, before 1 wins).

Maybe with the data displayed like that it can't be done? What if I setup the data differently as in Image nº 2?

In image 2, "Win Row" represents we have 3 wins in a row, before having 1 loss, and "Loss Row" represents we have 1 loss before a winner. It could be 2 loss in a row, etc.

So in this second example, the probability of having a winner, after 1 loss in a row, is 100%. (This shouldn't be used for predicting the future, only analyze the past data). How can I do this with a formula and larger sample size?

Thanks for taking the time

 

[jamesmoreno]

If the distribution in #5 is correct (i.e. if p is 80%, and trials are independent) then in an unbounded sample space, you expect
- 80% of wins to follow an immediately previous win
- 16% of wins to follow a single loss
- 3.2% of wins to follow a run of two losses
etc.

I suspect you're still looking for an answer to your original question, which might be phrased: I've experienced N losses in a row. What is my chance of a win next time?

The answer to that question is p, regardless of the value of N (as @shg said in post #2).

Hi Stephen, thanks for your reply!

I updated another reply with different spreadsheets, one that includes the winners, if you could kindly see it

I am not very good at math, or statistics, or excel. So bare with me please!

 

[jamesmoreno]

If the distribution in #5 is correct (i.e. if p is 80%, and trials are independent) then in an unbounded sample space, you expect
- 80% of wins to follow an immediately previous win
- 16% of wins to follow a single loss
- 3.2% of wins to follow a run of two losses
etc.

I suspect you're still looking for an answer to your original question, which might be phrased: I've experienced N losses in a row. What is my chance of a win next time?

The answer to that question is p, regardless of the value of N (as @shg said in post #2).

Yes, I was looking for a answer for this I've experienced N losses in a row. What is my chance of a win next time? and also reverse it, I've experienced N winners in a row. What is my chance of a loss next time?

I know this can't be used to predict the future though. I just wanted to analyze past data, to see what happened in the past. After N losses, (say 2 losses), how many times we went for a 3rd Loss, or for a winner?