MrPokemans
New Member
- Joined
- Dec 3, 2013
- Messages
- 10
Hey guys, I need your help.
The situation: I have 10 standard six-sided dice and want to roll values of 4, 5 or 6. This for example represents a game with 10 Goblins attacking a Troll, needing a 4 or higher to damage it. However, the Goblins are quick to strike so they can reroll their failed rolls once. So after having rolled the 10 dice the first time I take all the failed dice (which had values 1, 2 or 3) and roll them once more in an attempt to get more 4's, 5's and 6's.
When repeating this test a thousand times I'd expect the 10 dice to have 5,0 success rolls (on average) on their first attempt because there is a 50% for a dice to roll a 4 or higher. After the rerolls, I'd expect 7,5 success rolls because the chance has increased to 75%.
The question: Why are the 10 dice in my Excel sheet getting only about 5,8 success rolls on average, instead of the expected 7,5? Is there a mathematical explanation for this or do my formula's contain an error?
The formula's I'm using:
Column A
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
Column B
=IF(A1<4;RANDBETWEEN(1;6);A1)
=IF(A2<4;RANDBETWEEN(1;6);A2)
=IF(A3<4;RANDBETWEEN(1;6);A3)
=IF(A4<4;RANDBETWEEN(1;6);A4)
=IF(A5<4;RANDBETWEEN(1;6);A5)
=IF(A6<4;RANDBETWEEN(1;6);A6)
=IF(A7<4;RANDBETWEEN(1;6);A7)
=IF(A8<4;RANDBETWEEN(1;6);A8)
=IF(A9<4;RANDBETWEEN(1;6);A9)
=IF(A10<4;RANDBETWEEN(1;6);A10)
Column C
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&A2&A3&A4&A5&A6&A7&A8&A9&A10;3;"");2;"");1;""))
[cells C2 to C10 are empty]
Column D
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1&B2&B3&B4&B5&B6&B7&B8&B9&B10;3;"");2;"");1;""))
[cells D2 to D10 are empty]
Select cells A1 to D10 and drag the selection down to row 1.000 to copy the ten dice being rolled and rerolled a hundred times. Or drag them down to row 10.000 or 10.000 for even more iterations.
To conclude, if you select the entire column C, the numbers in the bottom right of he screen give an average value of between 4,8 and 5,2. This is the expected 50% chance to roll a 4 or higher in the first attempt, and in my sheet these values are confirmed.
However, when selecting column D, it shows an average of about 5,8 instead of the expected 7,5. How is that possible???
Any help would be highly apreciated!
Regards, Joris
The situation: I have 10 standard six-sided dice and want to roll values of 4, 5 or 6. This for example represents a game with 10 Goblins attacking a Troll, needing a 4 or higher to damage it. However, the Goblins are quick to strike so they can reroll their failed rolls once. So after having rolled the 10 dice the first time I take all the failed dice (which had values 1, 2 or 3) and roll them once more in an attempt to get more 4's, 5's and 6's.
When repeating this test a thousand times I'd expect the 10 dice to have 5,0 success rolls (on average) on their first attempt because there is a 50% for a dice to roll a 4 or higher. After the rerolls, I'd expect 7,5 success rolls because the chance has increased to 75%.
The question: Why are the 10 dice in my Excel sheet getting only about 5,8 success rolls on average, instead of the expected 7,5? Is there a mathematical explanation for this or do my formula's contain an error?
The formula's I'm using:
Column A
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
=RANDBETWEEN(1;6)
Column B
=IF(A1<4;RANDBETWEEN(1;6);A1)
=IF(A2<4;RANDBETWEEN(1;6);A2)
=IF(A3<4;RANDBETWEEN(1;6);A3)
=IF(A4<4;RANDBETWEEN(1;6);A4)
=IF(A5<4;RANDBETWEEN(1;6);A5)
=IF(A6<4;RANDBETWEEN(1;6);A6)
=IF(A7<4;RANDBETWEEN(1;6);A7)
=IF(A8<4;RANDBETWEEN(1;6);A8)
=IF(A9<4;RANDBETWEEN(1;6);A9)
=IF(A10<4;RANDBETWEEN(1;6);A10)
Column C
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&A2&A3&A4&A5&A6&A7&A8&A9&A10;3;"");2;"");1;""))
[cells C2 to C10 are empty]
Column D
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1&B2&B3&B4&B5&B6&B7&B8&B9&B10;3;"");2;"");1;""))
[cells D2 to D10 are empty]
Select cells A1 to D10 and drag the selection down to row 1.000 to copy the ten dice being rolled and rerolled a hundred times. Or drag them down to row 10.000 or 10.000 for even more iterations.
To conclude, if you select the entire column C, the numbers in the bottom right of he screen give an average value of between 4,8 and 5,2. This is the expected 50% chance to roll a 4 or higher in the first attempt, and in my sheet these values are confirmed.
However, when selecting column D, it shows an average of about 5,8 instead of the expected 7,5. How is that possible???
Any help would be highly apreciated!
Regards, Joris