The goal is to create a normal distribution graph with a specified mean and standard deviation. Start by entering those values in some cells in a worksheet. The example used to illustrate the process plots a graph with a mean of 20.8 and a standard deviation of 4.8 (see Exercises 1.98 to 1.109, p. 88). Enter those values in cells E1 and F1.
Start by setting up the x-values for a standard normal curve. In A1, enter the number -4.
Select cell A1, then select Edit | Fill | Series...
Set up the resulting dialog box as on the right. Using a step value of 0.25 is typically adequate. However, if you want more data points, use a smaller number, such as 0.1.
Next, in cell B1, enter the formula =A1*4.8+20.8. This converts the standard normal distribution to the distribution of interest. In C1, enter the formula =NORMDIST(B1,20.8,4.8,0). This provides y-values for the distribution of interest. Propagate B1:C1 down to cover all the rows that contain data in column A.
Plot columns B and C in a XY Scatter chart (smoothed lines without markers):
The result should be as given below:
-4 | 1.6 | 2.78813E-05 | 20.8 | 4.8 | ||||||||||||||||
-3.75 | 2.8 | 7.34574E-05 | ||||||||||||||||||
-3.5 | 4 | 0.000181809 | ||||||||||||||||||
-3.25 | 5.2 | 0.000422718 |
|
|||||||||||||||||
-3 | 6.4 | 0.000923302 | ||||||||||||||||||
-2.75 | 7.6 | 0.001894492 | ||||||||||||||||||
-2.5 | 8.8 | 0.003651729 | ||||||||||||||||||
-2.25 | 10 | 0.006612427 | ||||||||||||||||||
-2 | 11.2 | 0.011248118 | ||||||||||||||||||
-1.75 | 12.4 | 0.017974441 | ||||||||||||||||||
-1.5 | 13.6 | 0.026982832 | ||||||||||||||||||
-1.25 | 14.8 | 0.038051893 | ||||||||||||||||||
-1 | 16 | 0.050410568 | ||||||||||||||||||
-0.75 | 17.2 | 0.062736965 | ||||||||||||||||||
-0.5 | 18.4 | 0.073346943 | ||||||||||||||||||
-0.25 | 19.6 | 0.080555858 | ||||||||||||||||||
0 | 20.8 | 0.083112975 | ||||||||||||||||||
0.25 | 22 | 0.080555858 | ||||||||||||||||||
0.5 | 23.2 | 0.073346943 | ||||||||||||||||||
0.75 | 24.4 | 0.062736965 | ||||||||||||||||||
1 | 25.6 | 0.050410568 | ||||||||||||||||||
1.25 | 26.8 | 0.038051893 | ||||||||||||||||||
1.5 | 28 | 0.026982832 | ||||||||||||||||||
1.75 | 29.2 | 0.017974441 | ||||||||||||||||||
2 | 30.4 | 0.011248118 | ||||||||||||||||||
2.25 | 31.6 | 0.006612427 | ||||||||||||||||||
2.5 | 32.8 | 0.003651729 | ||||||||||||||||||
2.75 | 34 | 0.001894492 | ||||||||||||||||||
3 | 35.2 | 0.000923302 | ||||||||||||||||||
3.25 | 36.4 | 0.000422718 | ||||||||||||||||||
3.5 | 37.6 | 0.000181809 | ||||||||||||||||||
3.75 | 38.8 | 7.34574E-05 | ||||||||||||||||||
4 | 40 | 2.78813E-05 |