Transcript Practice Binomial & Poisson Distribution

Guide to Using Excel 2007 or 2010 For
Basic Statistical Applications
To Accompany
Business Statistics: A Decision Making
Approach, 7th Ed.
Chapter 5:
Discrete Probability Distributions
By
Groebner, Shannon, Fry, & Smith
Prentice-Hall Publishing Company
Copyright, 2008
Chapter 5 Excel Examples
Binomial Mean Catalog Sales
 Poisson Distribution –
Heritage Tile

More Examples
Binomial MeanCatalog Sales
Issue: People who order items from catalogs
can return the items for a refund. Historical
return rate for one catalog has been 11 percent.
Is this rate still valid?
Objective: Use Excel 2007 or 2010 to compute
binomial probabilities based on a sample of 300
purchases.
Binomial Mean – Catalog Sales
Situation:
• Sample Size is n=300
• p = .11
• Mean = np = 300(.11) = 33
• 44 returns were observed
• P(X > 44) = 1 – P(X < 43)
• Find P(X < 43) = ?
Binomial Mean – Catalog Sales
•Select Formulas tab
•Select More Functions
•Select Statistical
•Select BINOMDIST
Binomial Mean – Catalog Sales
Enter values:
Note:
•True = cumulative probability.
•False = exact probability
Binomial Probability Result
Poisson Distribution
Heritage Title
Issue: The distribution for the number of
defects per tile made by Heritage Tile is Poisson
distributed with a mean of 3 defects per tile. The
manager is worried about the high variability
Objective: Use Excel 2007 or 2010 to generate
the Poisson distribution and histogram to
visually see spread in the distribution of
possible defects.
Poisson Distribution – Heritage Tile
Enter values zero through 10
Poisson Distribution – Heritage Tile
Select Formulas,
More Functions,
Statistical and
POISSON
Poisson Distribution – Heritage Tile
Enter:
a1, 3, false
Poisson Distribution – Heritage Tile
Notice that I had pre-selected Cell
B1.
When I pressed enter the Poisson
Probability was loaded into that cell.
Simply copy and paste Cell B1 into
cells B2 : B11
Poisson Distribution – Heritage Tile
•Select the Insert tab
•Select Column
•Select the chart type that
you want
Poisson Distribution – Heritage Tile
Format the chart as
per Chapter 2
Creating A Binomial Table
Issue: The binomial tables in this text contain
specific probabilities for certain values of n and p.
You may need to have more extensive tables.
Objective: Use Excel 2007 or 2010 to generate the
Binomial table for n = 25 and p value of .01 to .50 in
increments of .01
Creating A Binomial Table
•Sample size in Cell B1
•p-values in Row 2
•x-values in Column B
Creating A Binomial Table
P(x =0) = .777821 for n = 25, p = .01
Notice the use of absolute cell
referencing – this allows you to copy
the function across and down to
complete this section of the binomial
table
Creating A Binomial Table
Copy the contents (formula) of Cell
C3 over the entire table
Creating A Binomial Table
Clear all Cells with a value of
Zero
Creating A Binomial Table
Repeat for the next set of values
for p: 0.11, 0.12 … 0.20
Simply change the contents of
Row 2.
Continue this for all possible
values of p.
For different sample sizes (n)
change Cell B1 and Row B
Creating A Poisson Table
Issue: The Poisson tables in this text contain
specific probabilities for certain values of λt . You
may need to have more extensive tables.
Objective: Use Excel 2007 or 2010 to generate
the Poisson Table table for λt = 6.0 to 7.0 in
increments of .10
Creating A Poisson Table
λt values in row 2
Values of x in column A
Creating A Poisson Table
P(x = 0) for λt = 6.0 equals .00248
Notice the use of absolute cell referencing this
allows you to copy the function across and down to
complete this section of the binomial table
Creating A Poisson Table
Continue this process for other λt
values as desired. As λt increases,
the possible values for x will have to
increase.