Need help in excel (STAT)

You must do your own work. Make sure to answer the final questions in your own words.

The examples shown below do not use the actual distributions. The real assignment is at the end. Please read the instructions carefully.

Example 1 Binomial (n = 20, p = 0.4)

 Put “x” in cell A1.

 Put “f(x)” in cell B1.

 In cells A2 through A4, enter “0”, “1”, and “2”.

 Highlight these last three cells. Put the cursor in the lower right corner of the cell and start drag- ging down until it shows the number “20”. Re- lease.

Lab 3: Discrete Distributions Stat 243 Spring 2015 due May 20

 Go to cell B2.

 Click on fx.

 Choose the Statistical category and then BINOM.DIST.

 For Number_s type “A2” (the location of the first X value).

 For Trials, type “20”.

 For Probability_s, type “.4”.

 For Cumulative, type “0”.

 Click OK.

 Put the cursor in the lower right corner of this cell and drag down to copy.

 Highlight all the cells containing probabilities. Click FormatCellsNumber, enter “5” for the number of decimal places, and click OK.

Example 2 Hypergeometric (N = 500, r = 100, n =15)

 Put “x” in cell D1.

 Put “f(x)” in cell E1.

 In cells D2 through D4, enter “0”, “1”, and “2”.

 Highlight these last three cells. Put the cursor in the lower right corner of the cell and start drag- ging down until it shows the number “15”. Re- lease.

 Go to cell E2.

 Click on fx.

 Choose the Statistical category and then HYPGEOM.DIST.

 For Sample_s type “D2” (the location of the first X value).

 For Number_sample, type “15”.

 For Population_s, type “100”.

 For Number_pop, type “500”.

 Click OK.

 Put the cursor in the lower right corner of this cell and drag down to copy.

 Highlight all the cells containing probabilities. Click FormatCellsNumber, enter “5” for the number of decimal places, and click OK.

Example 3 Poisson (μ = 5)

 Put “x” in cell G1.

 Put “f(x)” in cell H1.

 In cells G2 through G4, enter “0”, “1”, and “2”.

 Highlight these last three cells. Put the cursor in the lower right corner of the cell and start drag- ging down until it shows the number “30”. Re- lease. Remember that, in the Poisson distribu- tion, there is no upper limit for the X values, so at the end you will have to go back and de- termine where to end the list.

 Go to cell H2.

 Click on fx.

 Choose the Statistical category and then POISSON.DIST.

 For X type “G2” (the location of the first X val- ue).

 For Mean, type “5”.

 For Cumulative, type “0”.

 Click OK.

 Put the cursor in the lower right corner of this cell and drag down to copy.

 Highlight all the cells containing probabilities. Click FormatCellsNumber, enter “5” for the number of decimal places, and click OK.

 Now go back and find the first cell where the probability is zero to 5 decimal places. Delete all entries after this one. (Only do this for the Poisson example. For the other 2 cases, there is a highest value that X can take on.)

Lab 3 (due Monday, May 18)

1. A salesperson sees 25 clients, and has a 20% chance of making a sale each time. Generate the binomial distribution showing the probability of each possible number of sales, from 0 to 25.

2. A shipment contains 100 items, 20 of which are defective. You randomly select 10 items and test them. Generate the hypergeometric distribution showing the probability distribution for the number of defec- tive items that you find in the sample. Note that X ranges from 0 to 10.

3. A supermarket sees an average of 60 customers per hour. Generate the Poisson distribution showing the probability distribution for the number of customers arriving at the checkout line in a 15-minute time period. Since X ranges from 0 to infinity, follow the example shown above, to decide where to stop.

4. Show all of your results side-by-side on a single page, with all probabilities formatted to 5 decimal places.

5. In your own words, answer the following questions:

a) In #1, what is the most likely number of sales? What are the chances that the salesperson makes exactly that number of sales?

b) In #2, what are the chances that you will find at least one of the defective items in your sample?

c) In #3, suppose that it takes 5 minutes for the typical customer to go through the checkout line, so a single cashier can serve 3 customers in a 15-minute period. How many cashiers should be on duty, so that the store has at least a 90% chance of serving all of the customers? (Hint: make a second column for #3, this time setting "Cumulative" to 1 to get the cdf.)