XL: How to Create a Bell Curve Chart

ID: Q151352

The information in this article applies to:

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

  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" (without the quotation
     marks). Type "2000" (without the quotation marks) 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
     ------------------

     a. On the Insert menu, click Chart.

     b. 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."

     c. Click Next.

     d. In Step 2 of 4, click the Series tab.

        There will be one series, called "Frequency", listed on this tab.

     e. In the "Name" RefEdit box, delete the cell reference, and then
        highlight cell E15.

     f. In the "X Values" RefEdit box, delete the range reference, and
        then highlight the range E3:E10.

     g. In the "Y Values" RefEdit box, delete the range reference, and
        then highlight the range F3:F10.

     h. Click Add to add another series.

     i. Click in the "Name" RefEdit box, and then highlight cell E14.

     j. Click in the "X Values" RefEdit box, and then highlight the
        range E3:E10.

     k. Click in the "Y Values" RefEdit box, delete the value that's
        there, and then highlight the range G3:G10.

     l. Click Add to add another series.

     m. Click into the "Name" RefEdit box, and then highlight cell E16.

     n. Click into the "X Values" RefEdit box, and then highlight the
        range E3:E10.

     o. Click into the "Y Values" RefEdit box, delete the value that's
        there, and then highlight the range H3:H10.

     p. Click Finish.

        The chart will have two curved series and a flat series along the
        x-axis.

     q. 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.

     r. 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
     ------------------------------------

     a. On the Insert menu, click Chart, and then As New Sheet. Click
        Next.

     b. Click XY (Scatter), and then click Next.

     c. Click Chart type 6, and then click Finish. The chart will have
        two curved series and a flat series along the x-axis.

     d. 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.

     e. Click the Names and Values tab. In the Name box, type the
        following, and then click OK:

           =Sheet1!$E$14

     f. Double-click again with the series still highlighted and click
        the Patterns tab. Set the Line pattern to automatic and click OK.

     g. 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

     h. 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

For more information about charting in Microsoft Excel version 5.0, click the Search button in Help and type:

   chart

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 97
Keywords          : xlchart 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto kbinfo

Last Reviewed: January 8, 1999