1

Lab 3 Probability Distributions

Objectives: 1. To compute Normal probabilities and percentiles. 2. To compute t-distribution probabilities. Getting Started: Open Excel 2013 by clicking the Start Menu and typing “Excel 2013”. Click on the Excel Icon. When Excel opens, click Blank Workbook to start a new blank spreadsheet. In this lab we will learn about calculating probabilities for the Normal distribution. It is essential that you complete the experiments outlined below in order to complete the lab assignment. I recommend completing the examples and assignment in different worksheets within the same workbook (a new worksheet can be

created using ). Note: When inserting a function, you must always have a blank cell selected.

Experiment 1. Normal Distribution Probabilities and percentiles

Suppose heights of Canadian women are normally distributed with a mean of 63.6” and a standard deviation of 2.5”. Let X = the height of a randomly selected Canadian woman. Then X has a Normal distribution with mean μ=63.6 and standard deviation σ=2.5; that is, X has N(63.6, 2.5).

1. If we randomly select one woman from the population, what is the probability that she is 62” or shorter? We can compute this probability, ( ≤ ), using the NORM.DIST function as follows:

A. In the current worksheet, label cell A1 as Normal, then label cell A2 as P(X<=62). Next click cell B2, where we will calculate the probability.

B. Click the Formulas tab and select Insert Function. C. In the Insert Function dialog window, select the Statistical function category and the

NORM.DIST function. Click OK. D. Complete the NORM.DIST dialog window as shown below:

E. Click OK. The probability value 0.261086 should appear in B2.

2

2. What is the probability that a randomly selected Canadian woman is taller than 62”? That is, ( > 62) = ? Label cell A3 as P(X>62). Because ( > 62) = 1 − ( ≤ 62), we click in an empty cell, e.g. B2, and type = 1− B2 and press Enter to obtain the value for ( > 62).

3. What is the probability that a randomly selected Canadian woman’s height is between 62” and 67”? That is, ( ≤ ≤ ) =? Because this is the area between the values 62 and 67 under the normal curve, and (62 ≤ ≤ 67) = ( ≤ 67) − ( < 62 ), we will compute ( ≤ 67) and ( < 62) separately, and then calculate the difference as follows: First, label cell A4 as P(62<=X<=67), label cell A5 as P(X<=62) and cell A6 as P(X<=67). Then compute ( ≤ 62) and store its value in cell B5, which is done in item #1.Then in cell B6, compute ( ≤ 67) by first clicking in cell B6 and then following the same steps B– E in item #1, but replace the 62 in the X box in the NORM.DIST window with 67. The value 0.913085 should be returned in B6 for ( ≤ 67). Finally, click in B4, type =B6 – B5 and press Enter to get the value for (62 ≤ ≤ 67). The value 0.651999 should be returned in cell B4.

4. What is the cut-off height that defines the tallest 5% of the Canadian women population? That is, what is the 95th percentile of the distribution of Canadian women’s heights, N(63.6, 2.5)? We can compute this height (the 95th percentile) with the NORM.INV function as follows:

A. Label cell A7 as Normal Percentile. Label cell A8 as 95th Percentile. Click in cell B8, where we will calculate the probabilities.

B. Click the Formulas tab and select Insert Function. C. In the Insert Function dialog window, select the Statistical function category and the

NORM.INV function. Click OK. Complete the NORM.INV dialog window as shown below:

D. Click OK and the value 67.71213 should be returned in cell B8. This value says that about 95% of

all adult women are 67.71213 inches or shorter.

3

5. Suppose Karen’s height is 68 inches. How many standard deviations is Karen’s height above the women population mean of 63.6 inches? That is, what is the z-score of x = 68 in N(63.6, 2.5)? We can calculate this z-score by using the function STANDARDIZE as follows:

A. Label cell A9 as Z-score. Click in cell B9 where we will calculate the z-score. B. Click the Formulas tab and select Insert Function. C. In the Insert Function dialog window, select the Statistical function category and the

STANDARDIZE function. Click OK. Complete the STANDARDIZE dialogue window as shown below.

D. Click OK. A z-score of 1.76 should be returned in cell B9. This z = 1.76 says that Karen’s height is

1.76 standard deviations above the mean. She is not extremely tall because the z-value is not over 2.0.

6. Compute the cumulative probabilities for Standard Normal distribution using the NORM.S.DIST. Please follow the steps below to compute the left tail probabilities ( < ) and right tail probabilities P(Z > z):

A. In a new worksheet (insert a worksheet if there is not already a blank worksheet) in the present workbook, and prepare a table as shown to the right:

B. Let’s begin by computing the probability of getting a

z value that is less than -3.49. Click in cell B2. C. Click the Formulas tab and select Insert Function. D. In the Insert Function dialog window, select the Statistical function category and the

NORM.S.DIST function. Click OK. E. In the NORM.S.DIST dialog window, click in the Z box and click in cell A2 of the worksheet.

Enter TRUE in the Cumulative box. Click OK. The value 0.000242 should be returned in cell B2.

4

F. To calculate the cumulative probabilities for the remaining z values, follow the steps below:

 Click in cell B2.

 Copy the NORM.S.DIST function in cell B2 to cells B3 through B7. G. To compute the probabilities in column C, we can use the relationship of

( > ) = 1 − ( ≤ ) with the following steps:

 Click in cell C2 and enter the formula =1-B2 to obtain P(Z > -3.49).

 Copy the formula in cell C2 to C3 through C7 to obtain the remaining probabilities. 7. Compute z scores (percentiles) for specified probabilities using the NORM.S. INV function.

Please follow the steps below to find various percentiles of N(0, 1).

A. In a new worksheet or an existing worksheet, prepare a table as shown below:

B. Let’s begin by calculating the z value that defines the lower 1% of the Standard Normal distribution. Click in Cell B2.

C. Click the Formulas tab and select Insert Function. D. In the Insert Function dialog window, select the Statistical function category and the

NORM.S.INV function. Click OK. E. Enter 0.01 (for 1%) in the Probability box. Click OK. The z-score -2.32635 will be returned in

cell B2.

 To compute the z score for the lower tail 2.5%, click in cell B3, repeat steps C – D and enter the probability 0.025 in step E.

 To compute the z score for the upper tail 2.5% , click in cell B4, repeat steps C – D and enter the probability 0.975 (= 1 – 0.025) in step E.

 To compute the z score for the upper tail 1%, click in cell B4, repeat steps C – D and enter the probability 0.99 in step E.

5

Rules for Lab reports

1. Lab reports are to be submitted in either Microsoft Word or PDF format.

2. All Excel outputs that are necessary for supporting your answer of a question must be

included in your lab report.

3. Lab reports must be submitted in the Dropbox on D2L before the specified due time. No

late labs will be accepted.

4. Your class, name and the title of the report (e.g. Math 116-1, Sarah Smith, Lab 1) must

be clearly stated at the top of the first page of your report.

5. Labs are not group projects. Lab reports must be written independently. Pooling

answers together or copying answers from each other are NOT permitted.

Lab 3 Assignment

1. Suppose the Math 116 final exam scores are normally distributed with a mean of 66 points, and a

standard deviation of 12 points. Use Excel functions to find answers for the following questions. a) Suppose Jill’s Math 116 final exam score is 61 points. How many standard deviations is her score away from the mean? Name the Excel function that you used to find your answer. b) What percent of the class have scores lower than Jill’s? Higher than Jill’s? Name the Excel function that you used to find your answers. c) What percent of the class have scores between 70 and 90 points? Name the Excel function that you used to find your answer. d) Suppose the instructor decides that only the top 10% of the class will receive an A+. What is the cut-off exam score for an A+? Name the Excel function that you used to find your answer.

2. For the following questions, compute values related to the standard Normal distribution.

a) Use Excel functions to find the cumulative Standard Normal probabilities for the z-scores of -4.50, 1.34, and 2.00. Name the Excel function that you used to find your answers. b) Use an Excel function to find the 95th percentile of the Standard Normal. Name the Excel functions that you used to find your answer.