Summary
A bell curve is a plot of normal distribution of a given data set. This article describes how you can create a chart of a bell curve in Microsoft Excel.
More Information
n the following example you can create a bell curve of data generated by Excel using the Random Number Generation tool in the Analysis ToolPak. After Microsoft Excel generates a set of random numbers, you can create a histogram using those random numbers and the Histogram tool from the Analysis ToolPak. From the histogram, you can create a chart to represent a bell curve.
To create a sample bell curve, follow these steps:
-
Start Excel.
-
Enter the following column headings in a new worksheet:
A1:Original B1:Average C1:Bin D1:Random E1:Histogram G1:Histogram -
Enter the following data in the same worksheet:
A2: 23 B2: A3: 25 B3: STDEV A4: 12 B4: A5: 24 A6: 27 A7: 57 A8: 45 A9: 19
-
Enter the following formulas in the same worksheet:
B2: =AVERAGE(A2:A9) B3: B4: =STDEV(A2:A9)
These formulas will generate the average (mean) and standard deviation of the original data, respectively.
-
Enter the following formulas to generate the bin range for the histogram:
C2: =$B$2-3*$B4
This generates the lower limit of the bin range. This number represents three standard deviations less than the average.
C3: =C2+$B$4
This formula adds one standard deviation to the number calculated in the cell above. -
Select Cell C3, grab the fill handle, and then fill the formula down from cell C3 to cell C8.
-
To generate the random data that will form the basis for the bell curve, follow these steps:
-
On the Tools menu, click Data Analysis.
-
In the Analysis Tools box, click Random Number Generation, and then click OK.
-
In the Number of Variables box, type 1.
-
In the Number of Random Numbers box, type 2000.
Note: Varying this number will increase or decrease the accuracy of the bell curve.
-
In the Distribution box, select Normal.
-
In the Parameters pane, enter the number calculated in cell B2 (29 in the example) in the Mean box.
-
In the Standard Deviation box enter the number calculated in cell B4 (14.68722).
-
Leave the Random Seed box blank.
-
In the Output Options pane, click Output Range.
-
Type D2 in the Output Range box.
This will generate 2,000 random numbers that fit in a normal distribution.
-
Click OK.
-
-
To create a histogram for the random data, follow these steps:
-
On the Tools menu, click Data Analysis.
-
In the Analysis Tools box, select Histogram, and then click OK.
-
In the Input Range box, type D2:D2001.
-
In the Bin Range box, type C2:C8.
-
In the Output Options pane, click Output Range.
-
Type E2 in the Output Range box.
-
Click OK.
-
-
To create a histogram for the original data, follow these steps:
-
On the Tools menu, click Data Analysis.
-
Click Histogram, and then click OK.
-
In the Input Range box, type A2:A9.
-
In the Bin Range box, type C2:C8.
-
In the Output Options pane, click Output Range.
-
Type G2 in the Output Range box.
-
Click OK.
-
-
Create labels for the legend in the chart by entering the following:
E14: =G1&"-"&G2 E15: =E1&"-"&F2 E16: =G1&"-"&H2
-
Select the range of cells, E2:H10, on the worksheet.
-
On the Insert menu, click Chart.
-
Under Chart type, click XY (Scatter).
-
Under Chart sub-type, in the middle row, click the chart on the right.
Note: Just below these 5 sub-types, the description will say "Scatter with data points connected by smoothed lines without markers."
-
Click Next.
-
Click the Series tab.
-
In the Name box, delete the cell reference, and then select cell E15.
-
In the X Values box, delete the range reference, and then select the range E3:E10.
-
In the Y Values box, delete the range reference, and then select the range F3:F10.
-
Click Add to add another series.
-
Click the Name box, and then select cell E14.
-
Click the X Values box, and then select the range E3:E10.
-
In the Y Values box, delete the value that's there, and then select the range G3:G10.
-
Click Add to add another series.
-
Click the Name box, and then select cell E16.
-
Click the X Values box, and then select the range E3:E10.
-
Click the Y Values box, delete the value that's there, and then select the range H3:H10.
-
Click Finish.
The chart will have two curved series and a flat series along the x-axis. -
Double-click the second series; it should be labeled "- Bin" in the legend.
-
In the Format Data Series dialog box, click the Axis tab.
-
Click Secondary Axis, and then click OK.
You now have a chart that compares a given data set to a bell curve.
References
For more information about creating charts, click Microsoft Excel Help on the Help menu, type create a chart in the Office Assistant or the Answer Wizard, and then click Search to view the topic.