Can't find error in formula

[MrPokemans]

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

 

[igold]

Hi Joris,

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%.

Given that the second rolls are completely independent events from the first rolls, why do you think the chance has increased to 75%. Am I misunderstanding the scenario as you explained it.

igold

 

[shg]

This result meets expectations; you agree?

	A​	B​	C​	D​	E​
1​	5​	5​			A1: =RANDBETWEEN(1,6)
2​	2​	1​			B1: =IF(A1<4, RANDBETWEEN(1, 6), A1)
3​	6​	6​
4​	3​	3​		749​	D4: =COUNTIF(B1:B1000, ">=4")
5​	3​	4​		4.287​	D5: =AVERAGE(B1:B1000)
6​	3​	4​
7​	4​	4​
8​	1​	6​
9​	3​	2​
10​	4​	4​
990​	3​	3​
991​	6​	6​
992​	3​	3​
993​	2​	6​
994​	5​	5​
995​	6​	6​
996​	3​	5​
997​	2​	6​
998​	5​	5​
999​	2​	5​
1000​	2​	1​

 

[FDibbins]

After the rerolls, I'd expect 7,5 success rolls because the chance has increased to 75%.

Why would you expect the chance to increase?

If you have a 50/50 chance to get >=4, then that chance wont change, no matter how often you roll. That's like saying of you roll 10 dice, rolled once, you have 50% chance of >=4, but if you roll 1 dice 10 times, the odds change to 100%?

 

[shg]

Only the dice <4 are re-rolled. So about half are already >= 4, and half of the re-rolls are, so net 75%.

The expected average is 5 for the retained rolls, and 3.5 for the re-rolls, so 4.25 overall.

 

[MrPokemans]

Thanks guys! Shg is right, only the failed dice get to reroll so that would indeed increase their performance.

As FDibbins' countif shows, the dice rolling goes fine so the error is in the len and substitute part. I still think both approaches should work though, so I'll be back here once Ive found the fix.