Open the Excel program
In our last computer assignment we learned how to find the standard normal distribution up to the given score value a, that is we found P(Z < a). We used Excel instead of the standard normal distribution table and evaluated P(Z<-1.72) with the command =NORMDIST(1.72,B1,B2,TRUE) and we obtained 0.042716 approximately. Here Z is standard normal distribution. In this exercise we will learn how to go backwards, that is from the probability P(Z < a) to the score a.
In cell A1 type mean and in cell B1 type 0. That way we are letting mean be zero.
Next click on cell A2 and type SD and in cell B2 type 1. That means that we let standard deviation be one. That is, our normal distribution is standard.
Suppose we know that the percentile of the score is 4.2716%. We would like to find the corresponding score.
Type 0.042716 in B6. That is the given probability of Z<a. We would like to find the score a. In cell B7 type =NORM.INV(0.042716,B1,B2). That gives you the score a. We went backwards to -1.72. There is a way to find this score a using the standard normal distribution table, but our result will not be as precise as the one obtained with Excel today.
Now we do this process for any normal distribution. That is, not only that we do not need the table, but we also do not have to apply the formula to go from standard normal distribution to the given normal distribution. We illustrate that on an example. Suppose that cholesterol has normal distribution with mean 189 and standard deviation 28. Suppose your cholesterol percentile is 60%. That is your cholesterol is higher than approximately 60% of all people in the world. We would like to evaluate your cholesterol level using Excel. To do this we first click on cell D1and type 189. That means that we let mean be 189.
Next click on cell D2 and type 28. That means that we let standard deviation be 28. This means cholesterol normal distribution is not standard. Next, click on cell A6 and type probability. Then click on cell A7 and type score.
Now click on cell D6 and type .6
Finally click on cell D7 and type =NORM.INV(D6,D1,D2). Did you obtain approximately 196 as your cholesterol?
Exercises
Exercise 1 Using Excel, evaluate the score that corresponds to 65 percentile in standard normal distribution. Put your result in cell A11.
Exercise 2. Let X be normally distributed with mean 89 and standard deviation 15. Given P(X<a)=36% evaluate a using Excel. Put your result in cell A15.
