Can't find error in formula

MrPokemans

New Member
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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

igold

Well-known Member
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

MrExcel MVP
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

Well-known Member
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

MrExcel MVP
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

New Member
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,990
Messages
5,834,751
Members
430,315
Latest member
bobh63

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.

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

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