Simulate coin tossing question

[jmsmit5]

Hi,
I have a problem for a math class that I am supposed to do in Excel.
am supposed to simulate 50, 600, and 4000 coin tosses, and calculate th
number of heads (1) and tails (0) I get.

Here is the exact question:
a. Simulate 50 tosses of the coin by generating 50 random (integer
numbers between 0 and 1. Then calculate the mean of these 50 numbers.
THis mean gives you the proportion of 50 tosses that resulted in tails.
Using this proportion, calculate the number of heads and tails yo
obtained in 50 simulated tosses.
b. Repeat part a by simulating 600 tosses.
c. Repeat part a by simulating 4000 tosses.

I understand the formula for generating random numbers (for m
application) is =floor(rand()*(1-0+1)+0,1)

How do I repeat this formula 50, 600 and 4000 times? I really don'
want to copy and paste the formula into new cells 4000 times. And ho
does regenerating the data (F9) apply to this formula? I realize I a
getting random numbers everytime I hit F9, maybe the formula ha
something to do with repeating that function.

TIA,

Justi

 

[Ken Wright]

Why wouldn't you want to copy and paste a formula into 4000 cells, or even
40,000 cells. You make it sound like it would be an effort, yet in fact it
should take you no more than a few seconds. If I have a formula in say cell A1
and i copy it, I can then select cells A2:A4000 by using SHIFT and then either
the Down Arrow key, or I can type A4000 in the box above A1, hold down SHIFT and
then press ENTER and it will instantly select all those cells. I then just hit
Edit / Paste and I have replicated the formula in all those cells.

As far as calculation goes, the RAND() function is volatile and will recalculate
every time you hit F9 giving you a new number. If you want tohardwire the
numbers once you have got them (so they don't change anymore), then select the
whole column, do edit / Copy, then Edit / Paste special / Values.

If you have the analysis toolpak installed you can also just use

=RANDBETWEEN(0,1)

Failing that you could also just use

=ROUND(RAND(),0)

 

[OptionTrader]

Think the forumla for genrating random numbers (either 0 or 1) would b
=randbetween(0,1).

I don't know why you don't want to copy/paste 4000 times - it onl
takes seconds. You could use a VBA;

Sub Simulate_coin_Flip()
Cells.Select
Selection.ClearContents
Range("A1").Select

Dim cellnum

cellnum = 1
50:
Cells(cellnum, 1) = "=randbetween(0,1)"
cellnum = cellnum + 1
If cellnum < 51 Then GoTo 50
Cells(1, 2) = "Mean"
Cells(2, 2) = "=average(A1:A50)"
Cells(3, 2) = "Heads"
Cells(4, 2) = "=countif(A1:A50,1)"
Cells(5, 2) = "Tails"
Cells(6, 2) = "=countif(A1:A50,0)"

cellnum = 1
600:
Cells(cellnum, 3) = "=randbetween(0,1)"
cellnum = cellnum + 1
If cellnum < 601 Then GoTo 600
Cells(1, 4) = "Mean"
Cells(2, 4) = "=average(C1:C600)"
Cells(3, 4) = "Heads"
Cells(4, 4) = "=countif(C1:C600,1)"
Cells(5, 4) = "Tails"
Cells(6, 4) = "=countif(C1:C600,0)"


cellnum = 1
4000:
Cells(cellnum, 5) = "=randbetween(0,1)"
cellnum = cellnum + 1
If cellnum < 4001 Then GoTo 4000
Cells(1, 6) = "Mean"
Cells(2, 6) = "=average(E1:E600)"
Cells(3, 6) = "Heads"
Cells(4, 6) = "=countif(E1:E600,1)"
Cells(5, 6) = "Tails"
Cells(6, 6) = "=countif(E1:E600,0)"

End Sub

The procedure takes a while to run with automatic calulation switche
on. Much quicker to use manual so - click tools> options> calulation
then check "manual".

Good luck

 

[jay somerset]

The formula you plan on using is not valid. The mean of the tosses
will not give you "the proportion of 50 tosses that resulted in
tails", although the prediction will become better with a large number
of "tosses" in the average.

You actually need to count the number of random numbers that are
greater (or less) than 0.5 (assuming you use RAND() as your random
number generator. Use of an average is *not* the same as counting!

Example: with 4 tosses, you could easily get the following random
numbers (shown only to 2 decimal places here): 0.45 0.97 0.32 0.47.
The average here is 0.5525 -- clearly not representative of the number
of heads (<.5) vs tails (>.5).

With a very large sample, the probability of a false result using this
formula becomes very, very small, but the actual method you propose is
only an approximation.

 

[AlfD]

Hi!

Hang on!

OP said INTEGER random numbers between 0 and 1.

So he gets zeros or ones.

50 tosses which yield N ones (heads) having produced N/50 heads.
They will also produce a total score of N and and average of N/50.

More generally, P throws generating a total score of N give an averag
N/P which is precisely the proportion of heads in the sample.

Al

 

[Alex Delamain]

I must agree with AlfD

Using =round(rand(),0) does provide the correct result - ie 1's and 0'
which can then be summed or simply averaged to give the probability o
one result or the other.

I don't think this was in question in the original post - rather how d
you populate 4000 rows with the same formula without having to do it on
at a time which Ken Wright covered admirably (btw thanks I had neve
worked out a use for that box over A1

 

[jay somerset]

Hi!

Hang on!

OP said INTEGER random numbers between 0 and 1.

There are no integers between zero and one! The sentence itself
contained a contradiction, but as part of it was in parentheses, it
seemed less definitive.

I agree that if the RAND() results are rounded to the integral values
0 or 1, then averaging them will be OK.