Question
42192 views

How Can I Create a Bell Curve of My Data?

thomasm516 asked on

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

24 people had this question

Abuse history


The answered status icon Answer
joeu2004 replied on
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.
55 people found this helpful

Abuse history


progress