XL: How to Create a Bell Curve Chart

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

A bell curve is a plot of normal distribution of a given data set. To create a chart of a bell curve, use the Random Number Generation tool in the Analysis Tool Pack (ATP). After Microsoft Excel generates a set of random numbers, you can create a histogram using those random numbers and the Histogram tool from the ATP. From the histogram, you can create a chart to represent a bell curve.

MORE INFORMATION

To create a sample bell curve, follow these steps:

1. Enter the following data in a new worksheet:

   
          A1:Original B1:Average C1:Bin  D1:Random  E1:Histogram  G1:Histogram
             Data                           Range      Data          of Random
                                            Data                     Original
                                                                     Data
          A2: 23      B2:
          A3: 25      B3: STDEV
          A4: 12      B4:
          A5: 24
          A6: 27
          A7: 57
          A8: 45
          A9: 19
          A9: 19 




2. Enter the following formulas in the 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.



3. Enter the following formulas to generate the bin range for the histogram:

   C2: =$B$2-3*$B$4

   This generates the lower limit of the bin range. This number represents 3 standard deviations less than the average.

   C3: =C2+$B$4

   This formula adds one standard deviation to number calculated in the cell above. Fill the formula down from cell C3 to cell C8.



4. This step will generate the random data that will form the basis for the bell curve. On the Tools menu, click Data Analysis. In the Analysis Tools box, select Random Number Generation and click OK. In the Number of Variables box, type 1 . Type 2000 in the Number Of Variables box.
   
   (NOTE: Varying this number will increase or decrease the accuracy of the bell curve.) In the Distribution box, select Normal. In the Parameters group box, enter the number calculated in cell B2 in the Mean box, and the number calculated in cell B4 in the Standard Deviation box. Leave the Random Seed box blank. In the Output Options group box, select the Output Range button and enter D2 in the box. This will generate 2000 random numbers that fit in a normal distribution.



5. Click OK.



6. Create a histogram for the random data. On the Tools menu, click Data Analysis. In the Analysis Tools box, select Histogram, and click OK. In the Input Range box, type D2:D2001. In the Bin Range box, type C2:C8. In the Output Options group box, select the Output Range button and enter E2 in the box.



7. Click OK.



8. Create a histogram for the original data. On the Tools menu, click Data Analysis. In the Analysis Tools box, select Histogram, and click OK. In the Input Range box, type A2:A9. In the Bin Range box, type C2:C8. In the Output Options group box, select the Output Range button and enter G2 in the box.



9. Click OK.



10. Create labels for the legend in the chart by entering the following:

    E14: =G1&"-"&G2
    E15: =E1&"-"&F2
    E16: =G1&"-"&H2




11. Select the range of cells, E2:H10, on the worksheet.

    Microsoft Excel 97

    1. On the Insert menu, click Chart.
    
    
    
    2. In Step 1 of 4, click XY (Scatter). In the 5 charts listed under "Chart sub-type", in the middle row, click the chart on the right.
       
       Just below these 5 sub-types, the description will say "Scatter with data points connected by smoothed lines without markers."
    
    
    
    3. Click Next.
    
    
    
    4. In Step 2 of 4, click the Series tab.
       
       There will be one series, called "Frequency", listed on this tab.
    
    
    
    5. In the "Name" RefEdit box, delete the cell reference, and then highlight cell E15.
    
    
    
    6. In the "X Values" RefEdit box, delete the range reference, and then highlight the range E3:E10.
    
    
    
    7. In the "Y Values" RefEdit box, delete the range reference, and then highlight the range F3:F10.
    
    
    
    8. Click Add to add another series.
    
    
    
    9. Click in the "Name" RefEdit box, and then highlight cell E14.
    
    
    
    10. Click in the "X Values" RefEdit box, and then highlight the range E3:E10.
    
    
    
    11. Click in the "Y Values" RefEdit box, delete the value that's there, and then highlight the range G3:G10.
    
    
    
    12. Click Add to add another series.
    
    
    
    13. Click into the "Name" RefEdit box, and then highlight cell E16.
    
    
    
    14. Click into the "X Values" RefEdit box, and then highlight the range E3:E10.
    
    
    
    15. Click into the "Y Values" RefEdit box, delete the value that's there, and then highlight the range H3:H10.
    
    
    
    16. Click Finish.
        
        The chart will have two curved series and a flat series along the x-axis.
    
    
    
    17. 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 the "Secondary Axis" option in the "Plot Series On" group box.
    
    
    
    18. Click OK.
        
        You now have a chart that compares a given data set to a bell curve.
    
    
    

    Microsoft Excel versions 5.0 and 7.0

    1. On the Insert menu, click Chart, and then As New Sheet. Click Next.
    
    
    
    2. Click XY (Scatter), and then click Next.
    
    
    
    3. Click Chart type 6, and then click Finish. The chart will have two curved series and a flat series along the x-axis.
    
    
    
    4. 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 the Secondary Axis button in the "Plot Series On group" box.
    
    
    
    5. Click the Names and Values tab. In the Name box, type the following, and then click OK:

       =Sheet1!$E$14

    
    
    
    6. Double-click again with the series still highlighted and click the Patterns tab. Set the Line pattern to automatic and click OK.
    
    
    
    7. Double-click the first series; it should be the first "Frequency" labeled in the legend. In the Format Data Series dialog box, click the Names and Values tab. In the Name box, type the following, and then click OK:

       =Sheet1!$E$15

    
    
    
    8. Double-click the third series; it should be the only "Frequency" labeled in the legend. In the Format Data Series dialog box, click the Names and Values tab. In the Name box, type the following, and then click OK:

       =Sheet1!$E$16

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

REFERENCES

For more information about charting in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

creating and formatting charts

chart

Additional query words: 5.00a 5.00c 8.00 97

Keywords : xlchart
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,97; Win95:7.0; winnt:5.0
Platform : MACINTOSH Win95 WINDOWS winnt
Issue type : kbhowto kbinfo