Plotting Different Data On Established Bell Curve

runforitmarty · Feb 12, 2020

So I have a Bell Curve built with Excel 2016 that uses Bin values based on the average and standard deviation coupled with a Histogram. The graph works fine no problems at all.

However, I would like to put the original data points (with labels) that developed the average/standard deviation on the graph just as an FYI, but not impacting the graph itself.

Can this be done? If so can somebody explain it to me?

6StringJazzer · Feb 12, 2020

There are a couple of problems in doing this. For your distribution curve ("bell curve" specifically implies a normal distribution--is your data normal?) the x-axis is the bin value and the y-axis is quantity. For your raw data, it is not two-dimensional, just a list of values of the population (or sample). I don't know how could plot the original data points.

Take for example, a curve that shows the distribution of the height of a randomly selected group of men. It will be a nice bell curve. The data it is made up of is just a list of heights. You can't really plot a list of heights, except maybe as points on a line, and the density of the points in any given interval corresponds to the quantities in the bins you have defined. So such a plot doesn't give any additional information not already visible on the distribution curve.

Can you give more details about what this data represents? It would also help to see a screen shot of your existing graph.

runforitmarty · Feb 12, 2020

Your assumptions are correct. This is a normal distribution, The raw data is half a dozen dollar values (sample) that were used to create the average, standard deviation and Bin values used to generate the bell curve.

For instance, say one of the sample numbers was $8. If I wanted to put that $8 on the curve with a name next to it (say the curve has values ranging from $6.50 to $8.50) at the appropriate point could I do it?

6StringJazzer · Feb 13, 2020

I am sure there is a way to do something like this in Excel but I cannot visualize what you want it to look like. The curve has $ on the x axis and quantity on the y axis. Where on your graph would you want to place that point? On the curve at the x value for the bin in falls in? (Do you mean that your sample size is 6, which is quite small, or that your sample can have any one of 6 values?)

AhoyNC · Feb 15, 2020

Are you trying to do something like this?
Once you have your bell curve. Add another data series as an XY scatter chart. You can then right click on one of the data points. Choose Format Date and you will get an option to add data labels.
These links should help.

Plot X and Y Coordinates in Excel - EngineerExcel

If you have a scatter plot and you want to highlight the position of a particular data point on the x- and y-axes, you can […]

www.engineerexcel.com

How to improve your Excel Chart

What's on this page Custom data labels Improve your X Y Scatter Chart with custom data labels How to apply custom data labels in Excel 2013 and lat

www.get-digital-help.com

6StringJazzer · Feb 15, 2020

@AhoyNC that is a very nice chart! How did you plot the underlying bell curve?

AhoyNC · Feb 15, 2020

To get the bell curve all you need is the mean and St. Dev. (I'm assuming a normal distribution).
In this example for the range I used 4 standard deviations below and above the mean. Depending on the data I usually use somewhere around 4 to 6 standard deviations below and above the mean.
So the range I used for X in this example was .40 to 14.00. I incremented the X value by 1.00, but you could use any increment the gives a smooth curve when you plot.
Then use the NORM.DIST function to get the Y values for the graph (column E)
In the example below you would plot D3 to E17.
I used a scatter plot, but you could also use an area chart.

Book1