Question

any one here that can help me with a problem in excel?

Answer 1

Not if you don't present it.

Answer 2

You are going to roll four twenty-sided dice. If the rolls total to 20 or less, roll two more twenty-sided dice and add that to the total. For instance, if the total of my four die rolls is 32, then that is the score of the game. If the total is 17, then I roll two more dice and add that to the total. If these two bonus dice total 20, then the score of that game is 37. Of ten thousand games, what is the average score?

Answer 3

i have but no one help

Answer 4

anyway

Answer 5

i have it all done but the last part

Answer 6

The last part makes no sense. What 10,000 games are we talking about? Do we mean just 10,000 that you happen to have played?

Answer 7

So, we're doing a Monte Carlo simulation in excel, is that it?

Answer 8

i dont understand ur reference lol

Answer 9

Well, I'n not going to open a wild excel document. Did you account for the imbalance of 0 and 20? If you just used the Rand() function without alteration, you will not get it right.

Answer 10

ok well my problem is, how do i find all the games that equal <20 out of the 10000 games. I know there is a function to find them all some how without having to go through all 10k

Answer 11

You should produce your rolls with =RANDBETWEEN(1,20). =RAND() is harder to control.

I made 40,000 copies of this function and put them in A1:D10000

Did you get that far?

Answer 12

yes i have all the numbers i just need to know how to find all the ones that =<20.

Answer 13

my average was 42.4

Answer 14

Right. I'm just trying to make sure we're on the same page.

Okay, in column E -- E1:E1000, I summed the previous 4 columns. E1 contains =sum(A1:D1)

Let's do a quick check of reasonableness. What is the theoretical expectation for the average? Is your 42.4 a reasonable value? Is it close to the theoretical expectation?

Answer 15

yes its resonable

Answer 16

rolling 4 die with 1-20 yes 40 ish is about right

Answer 17

The theoretical expectation is 4 * 10.5 = 42. I'd say it's reasonable.

If you just want to COUNT the rolls less than 21, you can use the =COUNTIF() function. Mine looks like this:

=COUNTIF(E1:E10000,"<21")

Answer 18

If you want to identify ALL the rolls less than 21 and supply the two additional rolls, you can use a nice, ugly =if(). Mine looks like this in F1:F10000

=IF(E1<21,E1+RANDBETWEEN(1,20)+RANDBETWEEN(1,20),E1) <== Just the first one, of course.

Answer 19

ok i got it thanks so much

Answer 20

Expectation of rolls less than 21?

I hope it is obvious that there are 160,000 different possible rolls where we distinguish the four dice. Of these, 4,845 can be less than 21. This suggests an expected count of:

\(10000\cdot\dfrac{4845}{160000} = 302.813\)

Thus, you should roll, on average, 605.623 additional dice, increasing the total expectation by 605.623*10.5/10000 = 0.63590415

Frankly, I'm a little surprised that this scheme raises the expected mean by only 0.636!!!

Okay, that was enough fun for one exploration.