How do I create a bell graph for standard deviation?

[Guest]

I have 15 data points with a predetermined mean. How do I create a bell
graph to show the standard deviation lines and all of the data points along
the X axis?

 

[Jon Peltier]

Tushar Mehta has a tutorial on this topic:

http://tushar-mehta.com/excel/charts/normal_distribution/index.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Thanks for the info.

One more question: I created the bell graph, now how do I plot points on
the curve of the graph? I have 15 points that I need to show along the curve.

thanks,
Chris

 

[Jon Peltier]

Put the X and Y values of the 15 points into two columns, copy this
data, and use Paste Special (Edit menu) to add the data to the chart as
a new series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Ok, I wasn't clear on what I need to do, or maybe I don't understand. I have
15 points from a set of data that could represent X axis points based on how
I created the bell curve. I do not have any y axis data. I used the
"enumerating a set of points in a worksheet" from the tushar-mehta tutorial
way of creating my bell curve. I want to plot the 15 points that I have on
the bell curve that I created. I made the bell curve using:
mean = 68.44
stdev = 6.52
step value = 1
stop value = 15

Now my set of data that I want to plot on this graph are:
67.81
68.89
72.56
61.62
66.81
66.13
54.42
58.18
57.35
63.29
68.98
69.74
67.35
80.74
62.16
I want to show these points along the curve, but I do not know what values
to use for a y axis point for each one.

thanks for the help so far,
Chris

 

[Jon Peltier]

After following Tushar's example step-by-step, put your desired X values
into E3:E17. Put this formula into F3, and fill it down:

=NORMDIST(E3,$F$1,$H$1,FALSE)

This is the same formula that you used already in column C to provide
the distribution values for the X data in column B. Plot the values in E
and F on the chart with those for B and C. Use markers without
connecting lines (or else it looks like a tangled cat's cradle).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Excellent, thank you for all the help!

Chris

After following Tushar's example step-by-step, put your desired X values
into E3:E17. Put this formula into F3, and fill it down:

=NORMDIST(E3,$F$1,$H$1,FALSE)

This is the same formula that you used already in column C to provide
the distribution values for the X data in column B. Plot the values in E
and F on the chart with those for B and C. Use markers without
connecting lines (or else it looks like a tangled cat's cradle).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

I followed your instructions but I have a question, maybe it is stupid but I
would like to know if I should change the -4 that is placed in A2 to
correspond with my own values? I am just wondering what the x axis
represents on the bell curve? I have 35 values but if I change the -4, my
curve turns into a crazy design. I am not sure what I am doing wrong.

 

[Rene]

Following the example here I have a question.

I plotted the bell graph using the example; filled down to a34:f34
a b c e f
2 -4 2 6.69151E-05 my values 1.9702E-267

changed the mean to the mean of my data
plotted x/y series using my e/f data

I now have two bell graphs with Tushar's graph centered on 0 and mine
centered on my mean. How do I plot my values on Tushar's graph?

Thanks

 

[Rene]

Following this example
a b c d e f
-4 2 6.69151E-05 my values 1.9702E-267
(filled a2:f34)

plotted e/f as x/y on the chart

I now have two bell graphs side by side. How do I plot my values on
Teshar's example. I followed the "Enumerating a set of points in a worksheet"
His is centered over zero and my data is centered over my mean.

Thanks

 

[Rene]

Following this example
a b c d e f
-4 2 6.69151E-05 my values 1.9702E-267
(filled a2:f34)

plotted e/f as x/y on the chart

I now have two bell graphs side by side. How do I plot my values on
Teshar's example. I followed the "Enumerating a set of points in a worksheet"
His is centered over zero and my data is centered over my mean.

Thanks