Data Analysis with Microsoft Excel: Updated for Office 2007

(Tuis.) #1
Chapter 5 Probability Distributions 219

where p is the probability of the event
(such as getting a head) and n is the
number of trials. For example, to calcu-
late the probability of getting exactly 5
heads in 10 tosses, the formula is


P^1 Y 5525 a
10
5
b^1 0.5^25112 0.5^21102525 0.2461

or 24.6%. In other words, there is about
a 1 in 4 chance of getting exactly 5 heads
out of 10 tosses. To calculate the prob-
ability of getting 5 or fewer heads, we
add the probabilities for the individual
numbers: p(Y 5 0), p(Y 5 1), p(Y 5 2),
p(Y 5 3), p(Y 5 4), and p(Y 5 5).


The mean of the Binomial distribu-
tion is np. The standard deviation is
!np^112 p^2. For example, if we fl ip 100
coins with p 5 .5, the expected number
of heads is 100 3 .5 5 50 and the stan-
dard deviation is !0.5# 0 .5# 100 5 5.
a. If you toss 20 coins, what is probabil-
ity of getting exactly 10 heads?
b. If you toss 50 coins, what is the ex-
pected number of heads? What is the
standard deviation?
c. You toss 10 dice, recording a 1 for a
1 or a 2, and a 0 for a 3, 4, 5, or 6,
what is the expected total? What is
the standard deviation?
d. You toss 10 dice, recording a 1 for a
1 and a 0 for the other numbers, what
is the expected total? What is the
standard deviation?



  1. Excel includes the BINOMDIST function
    to calculate values of the binomial dis-
    tribution. The syntax of the function is
    BINOMDIST(number, trials, prob,
    cumulative)


where number is the number of successes,
trials is the number of trials, prob is the
probability of success, and cumulative is
TRUE to calculate the cumulative value of


the probability function and FALSE to cal-
culate the value for the specifi ed number
value. For example, the formula

BINOMDIST(5, 10, 0.5, FALSE)
returns the value 0.246. To calculate the
cumulative value (in this case the prob-
ability of getting 5 or fewer heads out of
10), use the formula

BINOMDIST(5, 10, 0.5, TRUE)
which returns the value .623. Thus,
there is a 62.3% probability of getting
5 or fewer heads out of 10. Use Excel to
answer the following questions:
a. What is the probability of getting ex-
actly 10 heads out of 20 coin tosses?
What is the probability of getting
10 or less?
b. What is the probability of getting 10
heads out of 15 coin tosses? What is
the probability of getting more than 10?
c. You toss 10 dice, recording a 1 for a
1 or 2, and a 0 for a 3, 4, 5, or 6.
If you total up your scores, what is
the probability of scoring exactly
3 points? What is the probability of
scoring 3 or fewer?
d. You toss 100 dice, recording a 1 for a
1 and a 0 for the other numbers, what
is the probability of recording a score
of exactly 20? What is the probability
of scoring 20 or less?
e. If you toss a coin 10 times, what is
the probability of recording 4, 5, or
6 heads?


  1. The mean of the Poisson distribution
    is l and the standard deviation is "l,
    where l is the expected count per
    interval.
    a. The number of accidents at a factory
    in a year follows a Poisson distribu-
    tion with an expected value of 10 ac-
    cidents per year. What is the value of
    l? What is the standard deviation?

Free download pdf