Question

Applies to

39163 views
# 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

12 people had this question

### Abuse history

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.

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

45 people found this helpful