# How Can I Create a Bell Curve of My Data?

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

## Question Info

Last updated June 4, 2019 Views 52,250 Applies to: Answer
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.

16 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.