Can't find error in formula

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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 3, 2013
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

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
Top