Now that
we'll take the simple coin toss simulation a
step further.
Suppose we want
to know:
1. What is the probability of getting
"heads" on seven out of ten tosses, and
2. What is the probability of getting
"heads" on at least seven
out of ten tosses?
As in the case
of the single coin toss, we can solve the
problem mathematically rather than through
simulation. In ten coin tosses there
are exactly 2^10, or 1024, possible outcomes
of headstails combinations. Of these,
exactly 120 have seven "heads", so the
probability of getting exactly seven "heads"
in ten coin tosses is 120/1024, or 11.7%.
Furthermore, 176 of the 1024 combinations
have 7, 8, 9, or 10 heads, so the
probability of getting heads on at least
seven tosses is 176/1024, or 17.2%.
For this
example, we use the same formula to simulate
a single toss that we used in the previous
example. In each of cells A15 through
J15, enter the formula "=RANDBETWEEN(0,1)"
as illustrated below.
In cell L15, sum
the results of columns A through J by
entering "=SUM(A15:J15)" as shown above.
This represents the number of "heads" that
came up in ten random tosses of a coin.
Now make 5,000
copies of row 15 in the rows below it.
Thus we have a Monte Carlo worksheetbased
simulation representing 5,000 iterations of
tossing a coin ten times.
To analyze the
results, we make use of the COUNT() and
COUNTIF() functions. As illustrated
below, in cell K5 we enter the formula
"=COUNT(L15:L5014)" to return the exact
number of iterations in the model. In
K6 we count the number of iterations which
returned exactly seven heads by entering the
formula "=COUNTIF(L15:L5014,7)". Cell
L6 returns the percent of all iterations
that resulted in seven heads ("=K6/K5)", and
in this particular instance the outcome was
11.2%  reasonably close to the expected
statistical outcome of 11.7%.
Cell K7 counts
the number of iterations which returned AT
LEAST seven heads. The formula in that
cell is "=COUNTIF(L15:L5014,">6")". L7
shows that result as a percent of all
iterations, and again our outcome of 16.6%
is reasonably close to the statistical
probability of 17.2%.
Prev

Contents

Next
