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.
 |
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
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.
|
 |
| 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). Copy B2:C2 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 on the left. Finally, format the chart to get a result similar to Figure 1 or as desired.
|
 |
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:. |
|
| 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
|
 |
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. |  |
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.
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
