Microsoft KB Archive/213930

= XL: How to Create a Bell Curve Chart =

Article ID: 213930

Article Last Modified on 5/28/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q213930



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
In 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: <pre class="fixed_text">  C2: =$B$2-3*$B4 This generates the lower limit of the bin range. This number represents three standard deviations less than the average. <pre class="fixed_text">  C3: =C2+$B$4 This formula adds one standard deviation to the number calculated in the cell above. </li> Select Cell C3, grab the fill handle, and then fill the formula down from cell C3 to cell C8.</li> To generate the random data that will form the basis for the bell curve, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Tools menu, click Data Analysis.</li> In the Analysis Tools box, click Random Number Generation, and then click OK.</li> In the Number of Variables box, type 1 .</li> In the Number of Random Numbers box, type 2000.

NOTE: Varying this number will increase or decrease the accuracy of the bell curve.</li> In the Distribution box, select Normal.</li> In the Parameters pane, enter the number calculated in cell B2 (29 in the example) in the Mean box.</li> In the Standard Deviation box enter the number calculated in cell B4 (14.68722).</li> Leave the Random Seed box blank.</li> In the Output Options pane, click Output Range.</li> Type D2 in the Output Range box.

This will generate 2,000 random numbers that fit in a normal distribution.</li> Click OK.</li></ol> </li> To create a histogram for the random data, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Tools menu, click Data Analysis.</li> In the Analysis Tools box, select Histogram, and then click OK.</li> <li>In the Input Range box, type D2:D2001 .</li> <li>In the Bin Range box, type C2:C8.</li> <li>In the Output Options pane, click Output Range.</li> <li>Type E2 in the Output Range box.</li> <li>Click OK.</li></ol> </li> <li>To create a histogram for the original data, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>On the Tools menu, click Data Analysis.</li> <li>Click Histogram, and then click OK.</li> <li>In the Input Range box, type A2:A9 .</li> <li>In the Bin Range box, type C2:C8 .</li> <li>In the Output Options pane, click Output Range.</li> <li>Type G2 in the Output Range box.</li> <li>Click OK.</li></ol> </li> <li> Create labels for the legend in the chart by entering the following: <pre class="fixed_text">  E14: =G1&"-"&G2 E15: =E1&"-"&F2 E16: =G1&"-"&H2 </li> <li>Select the range of cells, E2:H10, on the worksheet.</li> <li>On the Insert menu, click Chart.</li> <li>Under Chart type, click XY (Scatter).</li> <li>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."</li> <li>Click Next.</li> <li>Click the Series tab.</li> <li>In the Name box, delete the cell reference, and then select cell E15.</li> <li>In the X Values box, delete the range reference, and then select the range E3:E10.</li> <li>In the Y Values box, delete the range reference, and then select the range F3:F10.</li> <li>Click Add to add another series.</li> <li>Click the Name box, and then select cell E14.</li> <li>Click the X Values box, and then select the range E3:E10.</li> <li>In the Y Values box, delete the value that's there, and then select the range G3:G10.</li> <li>Click Add to add another series.</li> <li>Click the Name box, and then select cell E16.</li> <li>Click the X Values box, and then select the range E3:E10.</li> <li>Click the Y Values box, delete the value that's there, and then select the range H3:H10.</li> <li>Click Finish.

The chart will have two curved series and a flat series along the x-axis.</li> <li>Double-click the second series; it should be labeled "- Bin" in the legend.</li> <li>In the Format Data Series dialog box, click the Axis tab.</li> <li>Click Secondary Axis, and then click OK.</li></ol>

You now have a chart that compares a given data set to a bell curve.

<div class="references_section">