discrete probability distribution.

discrete probability distribution

The following assignment is to be done in Microsoft Excel, and then uploaded as one file to the dropbox labeled Excel Lab 3.

1. Open up a new worksheet in Excel; type your name in cell A1. (0.5 point)

2. (7.5 points) Powerball is a multi state lottery. The following probability distribution represents the cash prizes of Powerball with their corresponding probabilities, where the grand prize for this drawing was 15 million dollars. If you were to play the game over and over again, your expected net revenue would be the mean of the distribution. Use Excel to find the mean, variance and standard deviation cash prize for the following discrete probability distribution.

X (cash prize $) P(x)

15000000 0.0000000068

200000 0.0000002800

10000 0.0000017110

100 0.0001539960

7 0.0047789610

4 0.0078814630

3 0.0145011600

0 0.9726824222

Copy the table over to your Excel spreadsheet below your name. If your numbers change to scientific notation select the cells that need to be changed. Then select the comma style button under Number on the Home ribbon. Then select the increase decimal place button until you get all 10 decimal places. You may have to widen your column to see the labels and numbers correctly.

In the column next to the probabilities you want to find the mean winnings by multiplying X by the corresponding probabilities and then adding them up. To do this use the cell formula called the =SUMPRODUCT that will do this in one step. See below. Be careful w/ cell references if you start your table in a different spot.

Next find the variance and standard deviation of the winnings. Click into the cell in the next column to the right of the first probability (Column C if you are at the same starting point that I have). Find the value of X2*P(X) for each value, then sum them up, this will be the first part of the variance formula. I have done an example below, but you may have to change the cell reference numbers. In this example the first probability would have been in row 4. The variance will be the sum of X2*P(X) minus the mean squared, see below. The standard deviation is the square root of the variance. For example if your first x value of 15000000 was in cell A4 then you would use the following formulas. Of course you will have to change the cell reference numbers to wherever you have placed your first x value.

Make sure you label and highlight your 3 answers. Format your answers to 6 decimal places.

3. (5.5 points) The unemployment rate in September 2013 was 7.3%. Using Excel, create a Binomial distribution table if 10 employable adults are selected at random.

a. Below the previous problem, label a column “Unemployed X” and type in all possible x-values from 0 to 10 in that column.

b. In the next column label it “Binomial P(X)”

c. Click on the empty cell (B19 adjust if you started your table in a different cell) next to where your zero is.

d. Click on Insert then choose Paste Function. Or you can choose the icon fx.

e. Choose Statistical as the category.

f. Select BINOM.DIST, then click OK.

g. Complete the dialog box as shown below and click OK

h. Now copy that cell down column B so that you have a probability for each value of X.

i. Format your column to 6 decimal places, no scientific notation.

j. Highlight the cell that has the answer to the following question: What is the probability that exactly 2 out of the 10 adults will be unemployed?

4. (5.5 points) The number of students who seek assistance with their assignments is Poisson distributed with a mean of 10 per day. Using Excel, create a Poisson probability table.

a. Label Column C (to the right of the Binomial Table) “Students X” and type in x-values from 0 to 30.

b. Label Column D “Poisson P(X)”

c. Click on the empty cell (D19) next to where your zero is.

d. Click on Insert then choose Paste Function.

e. Choose Statistical as the category.

f. Select POISSON.DIST, then click OK

g. Complete the dialog box as shown below and click OK.

h. Now copy that cell down column D so that you have a probability for each value of X.

i. Format your column to 6 decimal places, no scientific notation.

j. Highlight the cell that has the answer to the following question: What is the probability that exactly 6 students in a day will seek assistance with their assignment?

5. (6 points) In a shipment of 24 keyboards, there are 5 that are defective (a success is a defective typewriter). Using Excel, create a Hypergeometric distribution probability table if a sample of 3 typewriters is to be selected.

a. Label Column E “Keyboards X” and type in x-values from 0 to 3.

b. Label Column F “Hypergeometric P(X)”

c. Click on the empty cell (F19) next to where your zero is.

d. Click on Insert then choose Paste Function.

e. Choose Statistical as the category.

f. Select HYPGEOM.DIST, then click OK

g. Complete the dialog box as shown below and click OK.

h. Now copy that cell down column F so that you have a probability for each value of X.

i. Format your column to 6 decimal places, no scientific notation.

j. Using the table you just created, find and highlight the cell that has the answer to the following question: What is the probability that at least one keyboard is defective. (Hint: You will have to use the =sum formula.)

TO ORDER FOR THIS QUESTION OR A SIMILAR ONE, CLICK THE ORDER NOW BUTTON AND ON THE ORDER FORM, FILL ALL THE REQUIRED DETAILS THEN TRACE THE DISCOUNT CODE, TYPE IT ON THE DISCOUNT BOX AND CLICK ON ‘USE CODE’ TO EFFECT YOUR DISCOUNT. THANK YOU