# Create a chart with best-fit bell curve for set of data

1. ## Create a chart with best-fit bell curve for set of data

Hello everyone,

I have percentage ranges and the number of times that values are within each range.
For instance, range -2% to 0% contains 3 values, range 0% to 2% contains 6 values, range 2% to 4% contains 2 values. Though in reality I have far more ranges than three.

I would need to draw a chart with one column per range, which also shows a bell curve of the results.
Something like the attached drawing.

If this is feasible, can you please explain how to do it?

Thanks,
E

example.png

2. ## Re: Create a chart with columns and a bell curve

If you attach a file with your data, I can show you how to do the chart.

Please see yellow banner at the top of the page.

3. ## Re: Create a chart with columns and a bell curve

I would need to add a bell curve to the column chart.

4. ## Re: Create a chart with columns and a bell curve

It looks to me like you want to find the best-fit normal distribution for your data. Is that correct? This is more than a chart problem. It requires some statistical analysis. I will take a look at this but in the meantime someone else may already have a solution in hand to offer.

5. ## Re: Create a chart with best-fit bell curve for set of data

Assuming the values in F2 and G2 represent your best fit normal distribution (so, I'm assuming you already know how to do that), it looks to me like you just need to compute the values for the normal distribution (probability density function PDF -- I expect you can use Excel's built in NORMDIST() function(s)), and then add them to the chart. Here's what I did:

1) Excel's NORMDIST() function cannot use the text strings in column C, so I add a column of corresponding numbers. I chose the midpoint for each bin (0.29, 0.27, etc.) and add those to I2:I36.
2) Then enter a NORMDIST() function in J2 =NORMDIST(I2,\$F\$2,\$G\$2,FALSE) (note the mix of relative and absolute references). Copy/paste/fill into J2:J36.
3) Add J2:J36 to the chart. You can copy/paste to do this, or you can use the Select Data dialog.
4) Select the new data series and change the chart type to a regular line chart to complete the column + line combination chart.

Is that what you need to do?

6. ## Re: Create a chart with best-fit bell curve for set of data

Sorry, I'm not sure I want to make it a statistical problem.
I calculated mean and standard deviation because a couple of tutorials I found on the web said they were needed. However, If I draw a curve based on the mean and the deviation from it, I don't get the result I need.
I'm not trying to chart the probability of events. I simply need a curve which represents the existing data.

I've tried the 'Scatter with smooth lines' but it's not shaped like a bell curve: For groups with frequency zero, the line is flat on the axis.

7. ## Re: Create a chart with best-fit bell curve for set of data

"I simply need a curve which represents the existing data." Your data is not a normal distribution, so it cannot be shown as a bell curve. The only way to do what you showed in your image above is to synthesize new data.

When you have a scatter plot of test points, Excel has built-in features to calculate trend lines. It can do linear, quadratic, logarithmic, etc., to automatically find the best fitting curve for the data. But a normal distribution (bell curve) is not one of those options, so the only way to do it is how MrShorty described.

8. ## Re: Create a chart with best-fit bell curve for set of data

Alright, I followed MrShorty's instructions and now have the chart as shown in the attached spreadsheet.
I'm not sure that I've done everything correctly, but I got a graph similar to what I was looking for.
I then changed the two axes' bounds so that the curve is above the bars, which probably means I'll have to manually adjust the bounds if the values change. I can probably deal with that.

Do you confirm that I've correctly followed the steps (except modifying the axes)?

9. ## Re: Create a chart with best-fit bell curve for set of data

That looks fine. I am not fond of using the secondary axis in this way, because I feel it obscures the comparison between the histogram and the bell curve, but it's your chart. If you like the way it turned out, then keep it.

10. ## Re: Create a chart with best-fit bell curve for set of data

It's just that I once saw a chart the had the bell curve above the histogram and it looked good to me.
I can always give it it a try with axes aligned.

Thanks to 6StringJazzer too!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1