Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 5 Probability Distributions 197

NORMSDIST(y) Returns the probability of Y # y for the standard
normal distribution.
NORMSINV(p) Returns the value y from the standard normal
distribution such that p(Y # y) 5 p.
NORMBETW(lower, upper, mean,
std_dev)

Calculates the probability from the normal

distribution with m 5 mean and s 5 std_dev for the

range lower # y # upper. StatPlus required.

For example, if you want to calculate the probability of a random variable

from a normal distribution with m 5 50 and s 5 4 having a value # 40, ap-

ply the Excel formula

5 NORMDIST(40, 50, 4, TRUE)

and Excel returns the value .00621, indicating that there is a 0.621% prob-

ability of a value less than or equal to 40 from such a distribution. The value

of the PDF at that point returned by the formula

5 NORMDIST(40, 50, 4, FALSE)

is .004382, which is the height of the probability distribution function at

that point in the PDF curve. On the other hand, if you want to calculate a

value on the PDF for a particular probability, you use the NORMINV() func-

tion. The formula

5 NORMINV(0.90, 50, 4)

returns the value 55.12621, indicating that in a normal distribution with m

5 50 and s 5 4, there is a 90% probability that a random variable will have

a value of 55.12621 or less.

Using Excel to Generate Random Normal Data

Now that you’ve learned a little about the normal distribution, you can use

Excel to randomly generate observations from a normal distribution. You’ll

start by creating a single sample of 100 observations coming from a normal

distribution with m 5 100 and s 5 25. To do this, you need to have the

StatPlus add-in installed on Excel.

To create 100 random normal values:

1 Open a new blank workbook in Excel, click cell A1, and type Normal

Data in the cell.