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
 

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
Joined
Jul 8, 2014
Messages
2,851
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,833
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,833
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.
 
Master Excel Bundle

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.

Forum statistics

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