Comparing tallies of different length win streaks (statistics question)

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,676
Office Version
  1. 365
Platform
  1. Windows
I'm not sure of the correct terminology for explaining my question, so please bear with me.

Suppose I have a random event with a binary outcome (win/lose) and I have a set of data showing how many times win streaks of various lengths occurred. Here's the data for 668 "games".
Code:
[FONT=courier new] #Wins     0    1    2    3    4    5    6    7    8    9   10   11   12   13   14
 Tally   59   36   30   18   12    4    7    4    5    6    2    0    3    1    2
 Exp   53.5 38.3 27.5 19.7 14.1 10.1  7.3  5.2  3.7  2.7  1.9  1.4  1.0  0.7  0.5
[/FONT]
The 0-win streak occurred 59 times, the 1-win streak 36 times, etc. The third line is the expected number of times each streak should have occurred based on the total number of games played (668) and wins (479) yielding a winning % of 71.7%.

I would like to calculate a 4th line showing the probability of each tally given the total number of games played and the current win%. For example, what are the odds that there would be 59 0-win streaks, 36 1-win streaks, 30 2-win streaks, etc.?

In the data above, the 1-win streak is 2.2 occurrences short and the 3-win streak is 2.1 occurrences short. These are almost equal in the absolute differences, but I would think that the probability for the 1-win streak based on 36 occurrences would be greater, possibly much greater, than for the 3-win streak with half as many occurrences. I would think that the variability would be inversely proportional to the number of occurrences. I would like to find a way to quantify that so I can determine the likelihood of each tally.

Can someone help me with the correct formula? I think it has something to do with normal distributions and the distance from the mean, but my statistics skills are not up to the task.

Thanks...
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
After a little more research, I think I might be making this harder than it needs to be.

My goal is just to be able to compare the tallies. I'd like some measure of how "far off" each tally is from the expected value. The Error line shows the absolute difference between the actual and expected tallies. I'd like to be able to calculate the relative differences so that I can rank the errors from low to high. For example, how does an error of +2.5 on a tally of 30 for 2-win streaks compare with an error of +5.5 on a tally of 59 for 0-win streaks?

It occurred to me that I can divide the absolute error by the expected tally to get a %Error.

[TABLE="class: grid, width: 803"]
<tbody>[TR]
[TD]#Wins[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]Actual Tally[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Expected Tally[/TD]
[TD="align: center"]53.5[/TD]
[TD="align: center"]38.3[/TD]
[TD="align: center"]27.5[/TD]
[TD="align: center"]19.7[/TD]
[TD="align: center"]14.1[/TD]
[TD="align: center"]10.1[/TD]
[TD="align: center"]7.3[/TD]
[TD="align: center"]5.2[/TD]
[TD="align: center"]3.7[/TD]
[TD="align: center"]2.7[/TD]
[TD="align: center"]1.9[/TD]
[/TR]
[TR]
[TD]Error (Act-Exp)[/TD]
[TD="align: center"]+5.5[/TD]
[TD="align: center"]-2.3[/TD]
[TD="align: center"]+2.5[/TD]
[TD="align: center"]-1.7[/TD]
[TD="align: center"]-2.1[/TD]
[TD="align: center"]-6.1[/TD]
[TD="align: center"]-0.3[/TD]
[TD="align: center"]-1.2[/TD]
[TD="align: center"]+1.3[/TD]
[TD="align: center"]+3.3[/TD]
[TD="align: center"]+0.1[/TD]
[/TR]
[TR]
[TD]%Error (Err/Exp)[/TD]
[TD="align: center"]+10.3%[/TD]
[TD="align: center"]-6.0%[/TD]
[TD="align: center"]+9.1%[/TD]
[TD="align: center"]-8.6%[/TD]
[TD="align: center"]-14.9%[/TD]
[TD="align: center"]-60.4%[/TD]
[TD="align: center"]-4.1%[/TD]
[TD="align: center"]-23.1%[/TD]
[TD="align: center"]+35.1%[/TD]
[TD="align: center"]+122.2%[/TD]
[TD="align: center"]+5.3%[/TD]
[/TR]
</tbody>[/TABLE]

Does this provide a reasonable relative measure of the various errors? Is an error of +5.5 on a tally of 59 for 0-win streaks really slightly greater than an error of +2.5 on a tally of 30 for 2-win streaks (+10.3% vs +9.1%)?

And is the +2.5 error for the 2-win streaks slightly greater on the plus side than the -1.7 error for the 3-win streaks is on the negative side (+9.1% vs -8.6%)?

Statistically, is this a valid measure of the relative errors?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top