excel 2010 – statistics
CHAPTER 6: NORMAL DISTRIBUTIONS
GRAPHS OF NORMAL PROBABILITY DISTRIBUTIONS (SECTION 6.1 OF
UNDERSTANDABLE STATISTICS)
A normal distribution is a continuous probability distribution governed by the parameters
μ (the mean) and σ (the standard deviation), as discussed in Section 6.1 of Understandable Statistics
. The Excel command that generates values for a normal distribution is
NORM.DIST(x,mean,standard deviation,cumulative
)
When we set the value of cumulative to FALSE, the command gives the values of the normal probability density function for the corresponding x value. You can type the command directly into the formula bar, or find it by using the Paste Function button on the standard toolbar, selecting Statistical in the drop box and scrolling to NORMDIST in the dialog box below. We filled in the entries that we will use in the next example.
Example
Graph the normal distribution with mean
μ=10 and standard deviation σ
=2.
Since most of the normal curve occurs over the values
μ-3σ, μ +3σ
,we will start the graph at 10 – 3(2) = 4 and end it at 10 + 3(2) = 16. We will let Excel set the scale on the vertical axis automatically.
Generate the column of x values
To graph a normal distribution, we must have a column of x values and a column of corresponding y values. We begin by generating a column of x values ranging 4 to 16, with an increment of 0.25. To do this, select Cell A2 and enter the number 4. Select Cell A2 again and use the
>Home>Fill>Series to open the dialog box shown next. Set the options in the dialog box as shown and then press OK.
46
You will see that Column A now contains the numbers 4, 4.25, 4.50, … all the way up to 16.
Generate the column of y values
Select Cell B2, and enter
=NORM.DIST(A2,10,2,False)
Press Enter. Select Cell B2 again and move the cursor to the lower right corner of the cell. When the cursor changes to a small black +, hold down the left mouse button and drag down the column until each Column-A entry has a corresponding Column-B entry. Release the left mouse button. All the y values should now appear.
47
Create the graph of a normal distribution
We will use a Line Graph to create the graph of a normal distribution. Select the data in column B. On the Insert Ribbon, select Line Graph and choose the first chart sub-type.
Right-click on the chart area and choose Select Data. Under Horizontal (Category) Axis Labels, click Edit. Select the cells in Column A, starting with Cell A2. Click OK twice.
48
Next, add a title and variable label to the x-axis. In the Chart Title, type
Normal Distribution, Mean = 10, St Dev=2
For Category (X) axis, type x.
Now your worksheet will contain the graph of the described normal distribution. Move the graph and size it to your liking. Notice that as you make the graph wider or taller, the labels shown on the x-axis might change.
We can also graph two (or more) normal distributions on the same graph. In the next display we generated y values for a normal distribution with mean 10 and standard deviation 1 in Column C. Then, we select both Column B and C before selecting the chart type.
49
STANDARD SCORES AND NORMAL PROBABILITIES
Excel has several built-in functions relating to normal distributions.
STANDARDIZE(x,mean,standard deviation)
returns the z score for the given x value from a distribution with the specified mean and standard deviation.
NORM.DIST(x1,mean,standard deviation,cumulative);
when cumulative is TRUE, this returns probability that a random value x selected from this distribution is ≤ x1, i.e. it returns P(x ≤ x1). This is the same as the area to the left of the specified x1 value under the described normal distribution. When cumulative is FALSE, it returns the height of the normal probability density function evaluated atx1. We used this function to graph a normal distribution.
NORM.INV(probability,mean,standard deviation)
returns the inverse of the normal cumulative distribution. In other words, when a probability is entered, the command returns the value from the normal distribution with specified mean and standard deviation so that the area to the left of that value is equal to the designated probability.
NORM.S.DIST(z
1) returns the probability that a randomly selected z score is less or equal the specified value of z1, i.e. it returns P(z ≤ z1). This is the same as the area to the left of the specified z1 value under the standard normal distribution. This command is equivalent to NORM.DIST(x1,0,1,true).
NORM.S.INV(probability)
returns the value such that the area to its left under the standard normal distribution is equal to the specified probability. This command is equivalent to NORM.INV(probability,0,1).
Each of these commands can be typed directly into the formula bar for an active cell or accessed by using the Paste Function button.
50
Examples
(a) Consider a normal distribution with mean 100 and standard deviation 15. Find the
z score corresponding to x
= 90 and find the area to the left of 90 under the distribution.
First we place some headers and labels on the worksheet. Then,
1. in Cell B3, enter =STANDARDIZE(90,100,15) 2. in Cell C3, enter =NORMDIST(90,100,15,true)
(b) Find the z score so that 10% of the area under the standard normal distribution is to the left of z.
Again, we put some labels on the worksheet. Then, since we are working with a standard normal
distribution, we use NORMSINV(0.1).
51
To find areas under normal curves between two values, we do simple arithmetic with the cumulative areas provided by Excel. For instance, to find the area under a standard normal distribution between -2 and 3, we would use NORM.DIST(3, 0, 1, true) to find the cumulative area to the left of 3 and then subtract the cumulative area to the left of -2, found using NORM.DIST(-2, 0, 1, true).
52
To find areas under normal curves to the right of a specified value, we subtract the cumulative area to the left of the value from 1. For instance, consider the normal distribution with mean 50 and standard deviation 5. Below is a worksheet in which the area to the right of 60 is found.
LAB ACTIVITIES FOR NORMAL DISTRIBUTIONS
1. (a) Use Excel to sketch a graph of the standard normal distribution with a mean of 0 and a standard deviation of 1. Generate x values in column A ranging from -3 to 3 in increments of 0.5. Use NORM.DIST to generate the y values in column B.
(b) Use Excel to sketch a graph of a normal distribution with a mean of 10 and a standard deviation of 1. Generate x values in Column A ranging from 7 to 13 in increments of 0.5. Use NORM.DIST to generate the y values in column B. Compare the graphs of parts (a) and (b). Do the height and spread of the graphs appear to be the same? What is different? Why would you expect this difference.
(c) Sketch a graph of a normal distribution with a mean of 0 and a standard deviation of 2. Generate x values in Column A ranging from -6 to 6 in increments of 0.5. Use NORM.DIST to generate the y values in Column B. Compare that graph to that of part (a). Do the height and spread of the graphs appear to be the same? What is different? Why would you expect this difference? Note, to really compare the graphs, it is best to graph them using the same scales. Redo the graph of part (a) using x from -6 to 6. Then redo the graph in this part using the same x values as in part (a) and y values ranging from 0 to the high value in part (a).
2. Use NORM.DIST or NORM.S.DIST plus arithmetic to find the specified area.
(a) Find the area to the left of 2 on a standard normal distribution.
(b) Find the area to the left of -1 on a standard normal distribution.
(c) Find the area between -1 and 2 on a standard normal distribution.
(d) Find the area to the right of 2 on a standard normal distribution.
(e) Find the area to the left of 40 on a normal distribution with
μ = 50 and σ
= 8.
(f) Find the area to the left of 55 on a normal distribution with
μ = 50 and σ
= 8.
(g) Find the area between 40 and 55 on a normal distribution with
μ = 50 and σ = 8.
h) Find the area to the right of 55 on a normal distribution with
μ = 50 and σ
= 8.
