Drawing a Normal curve
A graph representing the density function of the Normal probability
distribution is also known as a Normal Curve or a Bell Curve (see Figure 1
below). To draw such a curve, one needs to specify two parameters, the
mean and the standard deviation. The graph below has a mean of zero and a
standard deviation of 1, i.e., (m=0,
s=1). A Normal distribution with a mean of zero
and a standard deviation of 1 is also known as the Standard Normal
Distribution.
 |
Figure 1 -- Standard Normal Distribution
In Excel, there are multiple ways to draw this function.
Enumerating a set of points in a worksheet
Using named formulas to create the graph
Using the free add-in, Plot Manager
Understanding the Standard Normal
distribution and how it connects all other Normal distribution functions.
Enumerating a set of points in a worksheet
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 10 and a standard deviation of 2. Enter those
values in cells F1 and H1. |
 |
| Start by setting up the x-values for a standard normal
curve. In A2, enter the number -4. 
Select cell A2, 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 B2, enter the formula =A2*$H$1+$F$1.
This converts the standard normal distribution to the distribution of
interest.In C2, enter the formula =NORMDIST(B2,$F$1,$H$1,FALSE).
This provides y-values for the distribution of interest.
Copy B2:C2 down to cover all the rows that contain data in column A.
The result should look like Figure 2 (on the right).
|
 |
Figure 2
|
| Plot columns B and C in a XY Scatter chart (smoothed
lines without markers): |
 |
| The result should be as on the left. Finally, format
the chart to get a result similar to Figure 1 or as desired.
|
 |
Using named formulas to create the graph
One can use named formulas to create the chart. The advantage of this
method is that it doesn't use temporary cells in a worksheet to create the
chart. However, it might be a little more difficult to understand.
Put the information required to create the chart in cells in a worksheet.
| The first three cells contain information similar to
that provided in the 'Enumerating a set of points
in a worksheet' section above. The last item is analogous to
the step value used in the Fill dialog box. However, this time,
the number of points used in the chart is specified directly. |
 |
| Next, create the required names using Insert | Name
► Define... |
 |
| The result should look like:. |
| NormCurveMean |
=Sheet1!$D$2 |
| NormCurveMinVal |
=Sheet1!$D$1 |
| NormCurveNbrPoints |
=Sheet1!$D$4 |
| NormCurveStdDev |
=Sheet1!$D$3 |
| NormCurveXVals |
=NormCurveMean+NormCurveStdDev*(NormCurveMinVal+ABS(2*NormCurveMinVal)/(NormCurveNbrPoints-1)*(ROW(OFFSET(Sheet1!$A$1,0,0,NormCurveNbrPoints,1))-1)) |
| NormCurveYVals |
=NORMDIST(NormCurveXVals,NormCurveMean,NormCurveStdDev,FALSE) |
|
| Next, plot the normal curve. Click any cell that
does not contain data and has no adjacent cell with data.
Click the Chart Wizard and select the XY Scatter chart (sub type
Smoothed line with no markers) as in the 'Enumerating
a set of points in a worksheet' section. In the 2nd step of
the wizard, select the Series tab, then the Add button. Specify
the names as the x- and y-values. Finally, format the chart as
desired.
For more on how to use named formulas in a chart, see the Dynamic
Charts page of the author's web site at
www.tushar-mehta.com
|
 |
Using the free add-in, Plot Manager
This add-in makes it easy to plot a complete graph by entering the formula
for the graph in a single Excel worksheet cell.
| Suppose, the mean and standard deviation for the normal
chart are in B1 and D1: |
 |
In B2, enter the formula for a normal distribution function.
=NORMDIST(A2,$B$1,$D$1,FALSE).
In B5 and B6, enter the minimum and maximum x-values to plot. ±4
standard deviations translate to =-4*$D$1+$B$1 and =4*$D$1+$B$1, respectively.
| Now, use the Plot Manager add-in. |
 |
| Specify the parameters as shown on the right |
 |
| The add-in will add a new worksheet to the workbook.
On that sheet, in addition to working data will be a normal chart. |
 |
| Format the chart as desired; move it to another sheet
if that is more appropriate. |
 |
What is the advantage of Plot Manager?
It creates (x,y) data pairs after taking into account the local curvature of
the function. Then, it assigns more data points where the function is
curved and fewer points where the function is relatively straight.
Comparing two normal curves, where the one on the right is done by the Plot
Manager software, the uneven allocation of the data points becomes quite
evident.
 
Understanding the Standard Normal
distribution and how it connects all other Normal distribution functions.
A value, x, from a normal distribution specified by a mean of
m and and a standard deviation of
s can be converted to a corresponding value, z, in a standard
normal distribution with the transformation z=(x-m)/s. And, of course, in reverse, any value from a
standard normal graph, say z, can be converted to a corresponding value on a
normal distribution with a mean of m and and a
standard deviation of s by the formula x=m+z*s. Remember that the
standard normal distribution has a mean of 0 and a standard deviation of 1 --
i.e., (m=0, s=1).
The ability to carry out this transformation is very important since we can
do all our analysis with the standard normal distribution and then apply the
results to every other normal distribution, including the one that
is actually of interest. For example, to draw a normal curve with a mean
of 10 and a standard deviation of 2 (m=10,
s=2), we would draw the standard normal distribution
and just re-label the axis. The first figure below is the standard normal
curve and the next figure is the curve with (m=10,
s=2).
| Each value along the x-axis represents that many
standard deviations from the mean, which, for the standard normal
distribution, is zero. The 1 (or -1) is one standard deviation
from the mean. Similarly, the 3 (or -3) is three standard
deviations from the mean. |
|
| Exactly as in the case of the standard normal
distribution, the numbers on the x-axis represent the standard
deviations from the mean. Since the mean is 10 and the standard
deviation is 2, one standard deviation from the mean (±1s) yields the values 12 and 8 (10±1*2).
Similarly, ±3s yields the values 4 and 16. |
 |
Keywords: standard normal distribution bell curve population grade statistics
sample sampling mean cumulative probability density function
|
