Normal random variables

Description of activity and Excel instructions: Normal random variables

Dataset: none

Calculate a normal probability from a given z-score

• Open Calc with a new worksheet.
• Create a table with the given values:
  • Make the row and column labels: pick a cell and type "Statistic" and one cell to the right, type "Value".
  • In each successive cell underneath Statistic, type "Population Mean", "Population Standard Deviation", "X", "z-score", "P(X=700)".
  • Enter the population mean, 507, in the Value column.
  • Enter the population standard deviation, 111, and the X value, 700.

To calculate the z-score,

• Enter the equation z = (X - mean)/stdev. (In this case, we get z = (700-507)/111, or 1.738739.)

To calculate P(X=700), we'll use the Calc function called NORMSDIST (note the spelling--be careful, there is also a function called "NORMDIST"), that takes a z-score and uses it to calculate a probability. This step takes the place of having to look up the probability in a normal table.

• In the cell next to P(X=700), type "=NORMSDIST(1.738739)" (or use the cell reference instead of typing the z-score).

Calc tells us that the probability is 0.95896.

Return to the OLI's Normal random variables page to complete the interpretive exercises on this part of the "Learn by doing" exercise.

Calculate a z-score from a given probability

We continue with the table created above, but this time we're looking for: z-score and X. Basically, we're going to do the inverse of the procedure we described above. Instead of being given an X and asked to find the probability, we're given a probability and asked to find the corresponding X value.

To calculate the z-score given a probability, we'll use the CALC function called NORMSINV, that takes a probability and uses it to calculate a z-score. This function is the inverse of the NORMSDIST function that we used previously, and takes the place of us having to look up the z-score in a normal table.

• In the column labeled Statistic', type "P(Z=z)".
• Under Value, type ".98".
• In the next row, under Statistic, type "z-score".
• Under Value, type "=NORMSINV(0.98)". (Calc tells us that the z-score is 2.053748)

To calculate the X value that corresponds to that z-score, solve for X in the equation z = (X - mean)/stdev.

• In the next row, under Statistic, type "X".
• Under Value, enter the formula for X. (Solving for X, we get X = 2.053748 * 111 + 507, or 734.966.)

Return to the OLI's Normal random variables page to complete the interpretive exercises.