Share via

How Can I Create a Bell Curve of My Data?

Anonymous
2011-02-27T11:29:03+00:00

I have the following data that I would like to put into a bell curve.  The chart should come out looking like one of the bell curves on this chart:  https://picasaweb.google.com/lh/photo/Wfaq1W-_o1w2EEDHG4BSlA?feat=directlink

The problem is that the example chart shown in that link was dummied up using arbitrary numbers to make it look good.  However, the numbers below are real data (Age in days of outstanding work order tickets), and I have been unable to figure out how to smooth out the curves into a nice bell.

Eventually, I'd like to have two bell curves on the same chart, but for now I will settle for just getting one to work right.

Any help will be greatly appreciated.

184
163
130
110
93
86
83
83
82
76
74
68
52
46
46
46
46
46
46
45
45
45
45
45
45
41
40
40
38
37
37
37
37
37
37
36
36
36
36
36
36
36
36
36
36
35
33
32
32
32
32
32
32
32
32
32
32
32
32
31
31
31
31
31
31
31
31
31
31
31
31
31
31
31
30
30
26
26
26
26
26
26
26
26
26
25
25
25
25
25
25
25
25
24
23
23
20
20
20
20
20
18
18
18
18
18
18
18
18
17
17
16
13
13
12
12
12
12
12
11
11
11
11
11
10
51
48
47
47
37
12
122

--Tom

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-02-27T19:36:38+00:00

    Tom wrote:

    > I have the following data that I would like to put into a bell curve.

    Charting frequency data is an art form; expect to experiment.  But note:  it is easy to misrepresent the shape of the data by choosing the "wrong" bin limits.

    That said....  Assuming your data are in A1:A132, create the following formulas:

    B1:  =MIN(A1:A132)

    B2:  =MAX(A1:A132)

    B3:  =AVERAGE(A1:A132)

    B4:  =STDEVP(A1:A132)

    (You might use STDEV instead if the data are a random sampling from a larger population.)

    Start with the assumption that the data are normally distributed.  In that case, 99.99% of the data population should fit between -4sd and +4sd from the mean.

    So in B5, compute -4sd:  =B3-4*B4.  And in B6, compute +4sd:  =B3+4*B4.

    In B7, compute the bin step size so that the 16th bin limit will be +4sd :  =(B6-B5)/15.

    Now, create 17 bin limits in B9:B25 as follows:

    B9:    =B5    (-4sd)

    B10:  =B9+$B$7

    Copy B10 down through B24

    B25:  =B2    (max)

    Now, create the frequency distribution in C9:C25 as follows.  Select C9:C25 and enter the following array formula (press ctrl+shift+Enter instead of just Enter):

    =FREQUENCY(A1:A132,B9:B24)

    Note that we purposely do not include the bin limit in B25.  That is just for charting purposes.  FREQUENCY will fill in C25 with the excess count.

    You can now select B9:C25 (both columns) and use the Chart Wizard to create an XY Scatter chart with smooth lines without markers.

    If your chart does not resemble a bell-shaped curve to some degree, skip the following steps.  Instead, you might want to consider changing your bin limits to something other than -4sd to +4sd, for example using the step size (B2-B1)/15, based on the difference between max and min.

    If your chart does resemble a bell-shaped curve, you might want to see how close it is to a normal distribution.  The following formulas construct the frequency table for a normal distribution that fits the data (count, mean and sd).

    C27:  =SUM(C9:C25)

    D9:    =NORMDIST(B9,B3,B4,1)*C27

    D10:  =(NORMDIST(B10,$B$3,$B$4,1)-NORMDIST(B9,$B$3,$B$4,1))*$C$27

    Copy D10 down through D24

    D25:  =(1-NORMDIST(B24,$B$3,$B$4,1))*$C$27

    Use the Chart Wizard to create a second series with B9:B25 for the X-axis and D9:D25 for the Y-axis.

    10+ people found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-06-14T08:53:22+00:00

    See if this helps:-

    http://support.microsoft.com/kb/213930

    If it does please Vote As Helpful.

    Thanks.

    It is deprecated and does not apply to Excel 2007+

    0 comments
  2. Anonymous
    2011-02-27T19:05:30+00:00

    Excel 2010 Tables

    A Chi-Squared Distribution fits your data much better

    than a Normal Distribution

    since age can never be less than zero.

    With ZunZun curve fitting.

    http://c3017412.r12.cf0.rackcdn.com/02_27_11.xlsx

    If you get *.zip, don't unzip, just rename *.xlsx

    http://www.mediafire.com/file/epsayw0275a3gih/02_27_11.pdf

    0 comments
  3. Anonymous
    2011-02-27T12:22:14+00:00

    Assuming that you 132 data points are located in cells A1:A132, this is one way you can try.

    In cell B1 you put a number. Start with 15, you can experiment with this.

    In cell B2 you put a number. Start with 0, you can experiment with this.

    In cell B3 you put the formula =B2+B$1

    Copy cell B3 down to cell B22 (you can experiment with this as well)

    Select cells C2:C22 and type in the formula =FREQUENCY(A1:A132,B2:B22)

    Note: You should confirm the formula by CTRL+SHIFT+ENTER (not just ENTER)

    The entire range C2:C22 will now be filled with data.

    Select the range B2:C22 and click on Scatter with Smooth Lines in the Chart section of the Insert Tab.

    Now you will get a smooth curve from your data.

    Try changing the values in B1 and B2 until you find the curve that you want.

    Hope this helps / Lars-Åke

    0 comments
  4. triptotokyo-5840 36,681 Reputation points Volunteer Moderator
    2011-02-27T12:11:04+00:00

    See if this helps:-

    http://support.microsoft.com/kb/213930

    If it does please Vote As Helpful.

    Thanks.

    0 comments