JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,525
- Office Version
- 365
- Platform
- Windows
This is really a statistics question. If this is inappropriate, let me know. I've tried getting help in a couple of statistics forums, but I always get better answers here and I know Excel is often used for statistical calculations.
I am interested in comparing the "tally errors" is different "win streaks" in a random event, like a game, with a given probability of a win. The table shows results for win streaks of various lengths (Row 5). Columns D-G contain data for these 4 streak lengths over 4,000 games with an overall winning % (Wins/Games) of 60%.
Col H shows the formulas used for the calculations.
<tbody>
</tbody>
Row 6 shows the expected probability for each streak length. Row 7 shows the expected tally for each streak length. Row 8 shows the actual tallies. Row 9 shows the difference in these percentages and Row 10 shows the percentage error.
My question is this: Are the values in Row 10 the best measures of how closely the actual tallies (Row 8) are to the expected tallies (Row 7)? If there is a better measure, I would appreciate some help understanding what it is and how to calculate it. I am looking for a measure that will allow me to rank the various tallies according to the error from the expected.
I hope I explained that adequately.
I am interested in comparing the "tally errors" is different "win streaks" in a random event, like a game, with a given probability of a win. The table shows results for win streaks of various lengths (Row 5). Columns D-G contain data for these 4 streak lengths over 4,000 games with an overall winning % (Wins/Games) of 60%.
Col H shows the formulas used for the calculations.
R/C | C | D | E | F | G | H |
3 | #Games | 4000 | 4000 | 4000 | 4000 | D3: 4000 |
4 | Win % | 60.00% | 60.00% | 60.00% | 60.00% | D4: 0.6 |
5 | Streak Length | 1 | 2 | 3 | 4 | D5: 1 |
6 | Exp Streak % | 24.00% | 14.40% | 8.64% | 5.18% | D6: =(D4^D5)*(1-D4) |
7 | Expected Tally | 384.00 | 230.40 | 138.24 | 82.94 | D7: =D6*D3*(1-D4) |
8 | Actual Tally | 390 | 227 | 141 | 81 | D8: 390 |
9 | Error (Act-Exp) | 6.00 | -3.40 | 2.76 | -1.94 | D9: =D8-D7 |
10 | Error % | 1.54% | -1.50% | 1.96% | -2.40% | D10: =D9/D8 |
<tbody>
</tbody>
Row 6 shows the expected probability for each streak length. Row 7 shows the expected tally for each streak length. Row 8 shows the actual tallies. Row 9 shows the difference in these percentages and Row 10 shows the percentage error.
My question is this: Are the values in Row 10 the best measures of how closely the actual tallies (Row 8) are to the expected tallies (Row 7)? If there is a better measure, I would appreciate some help understanding what it is and how to calculate it. I am looking for a measure that will allow me to rank the various tallies according to the error from the expected.
I hope I explained that adequately.