Guide to Using Excel 2007 For Basic Statistical
Guide to Using Excel 2007 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 8 th Ed. Chapter 5: Introduction to Discrete Probability Distributions By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2011
Chapter 5 Excel Examples Binomial Mean Catalog Sales n Poisson Distribution – Heritage Tile n More Examples
Binomial Mean. Catalog 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 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 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: a 1, 3, false
Poisson Distribution – Heritage Tile Notice that I had pre-selected Cell B 1. When I pressed enter the Poisson Probability was loaded into that cell. Simply copy and paste Cell B 1 into cells B 2 : B 11
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 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 B 1 • 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 C 3 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 B 1 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 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.
- Slides: 23