Normal distributions -- basic calculations
The goal of this lab is to compute normal probabilities and inverse normal probabilities using Excel.The Excel functions we consider are:
=NormSDist(a) which computes the probability that z ≤ a in the Normal Standard distribution
=NormDist(a,m,s,0) which computes Pr(a) in the normal distribution with mean m and standard deviation s,
=NormDist(a,m,s,1) which computes the cumulative Pr(x≤a) in the normal distribution with mean m and standard deviation s, (Note: the cumulative probability is obtained when the 4th argument is set to 1 instead of 0.)
=NormInv(p,m,s) gives the value of x so that NormDist(x,m,s,1) equals p in the normal distribution with mean m and standard deviation s,
Problem 1. In this problem you will compute Pr(a≤z≤b) for values of a and b using the Standard Normal distribution. The probability that z lies between a and b is found by substracting the cumulative probabilities, that is
=NormSDist(b) - NormSDist(a)
A. Compute Pr(-1≤z≤1) by entering =NormSDist(1) - NormSDist(-1) in a cell. Label the probability.
B. Now compute Pr(-2≤z≤2) in another cell. Label this probability as well. In a text box explain a "rule-of-thumb" suggested by these two calculations.
Although we used the NormSDist function above the same process can be used to compute Pr(a≤z≤b) for general normal distributions using NormDist(x,m,s,1).
Problem 2. Suppose X is normal distribution with mean 100 and standard deviation 15.
A. Compute Pr(x≤120) using appropriate values in NormDist(x,m,s,1).
B. Use the transformation z=(x-m)/s to convert 120 for the normal distribution to an appropriate z-value for the Standard Normal distribution. Label the x and z values.
C. Compute Pr(z≤b) where b is the z-value found in Part B. (Remember to use the NormSDist function for this.) Label the probability. Compare the results in Parts A and C and in a text box explain the significance.
Problem 3. In this problem you are to compare the shapes of two normal distributions with mean 100. One has standard deviation 10 and the other 5. In a new sheet enter numbers from 70 to 130 in column A using rows 3 to 63. For the first distribution enter =NormDist(A3,100,10,0) in cell B3. Enter a similar formula in cell C3 that gives the values of a distribution with standard deviation 5. Copy the formulas in cells B3 and C3 to apply to all values in the range A3:A63. Graph the two functions in columns B and C in the same chart using the values in column A as the x-values. The Chart type should be a "Scatter Diagram with points connected by Smooth lines Without Markers". In a text box compare the graphs of the two distributions.
Problem 4. In this problem you are to see how the NormInv function works. Let X be a normal distribution with mean 100 and standard deviation 20.
A. We want to find the x so that 40% of the distribution values are below it, that is, we want to find the value that gives the 40th percentile. To find this x, enter the formula =NormInv(0.4,100,20) in a cell and label it. Next, check that it works by computing NormDist(x,100,20,1) where x is the value just found. What does it give? Lable it.
B. Find the x so that 20% of the distribution values exceed it. Label the value and check it.
Problem 5. You now apply what you have learned. A company finds that the length of phone calls to its tech support staff is normally distributed with a mean of 9.6 minutes and standard deviation of 2.8 minutes. Find (and label your answers):
A. the probability a call lasts between 8 to 12 minutes.
B. the percent of call that last less than 5 minutes.
C. the percent of calls that last more than 15 minutes.
D. the length of time the longest 10% of the calls take.