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

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.

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.

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?

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

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.