Vertical ND Curve on Combination Chart

[Guest]

I would like to plot a normal distribution curve horizontally (on the Y axis)
in a chart with vertical data histogram plots (up and down) and horizontal
line plots (+/- 1 SD) – all currently using the Y axis. I have the
histogram/SD portion built. Is there a way to use a secondary axis on the
left side of the chart for the nd curve, yet have the nd curve appear on the
right side of the chart? Actually, it may not make a difference just which
axis, Y or secondary, the plots are attached to so long as the plots can be
placed on the chart in an orderly fashion to display three issues: histogram
data, lines for +/- 1 SD, nd curve - the SD lines must be logical with the nd
curve. The next question: how would I shade the area of +/- 1 SD?

Thanks,
Phil

 

[Tushar Mehta]

In the subject you mention a vertical ND curve but in the body you
mention horizontally. So, it is not clear what you want to do.

In any case, you might want to check http://www.tushar-
mehta.com/excel/tips/index.html specifically the link for 'Invert and
rotate charts'

To draw straight lines that match up with pre-determined values see
http://www.tushar-mehta.com/excel/charts/straight_lines/index.html
Also, Jon Peltier has an alternative approach on his site.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Tushar Mehta,

Sorry for the confusion. I have plotted 15 data points as vertical columns -
they are positioned up-and-down, using the Y axis for values. Next, I
plotted +/- 1 standard deviations and the mean value as horizontal lines
(they run straight across the chart), also using the Y axis for values. What
I want to do is plot a normal distribution curve such that the nd curve mean
and SDs are all synchronous with the three horizontal lines already on the
chart. The nd curve apex is right and it opens to the left. I want the
viewer to see how the three lines across the chart align with the nd curve.

Thanks, Phil

 

[Michael R Middleton]

Phil Hageman -

Jon Peltier has some examples of adding lines to charts at
www.peltiertech.com, and you might be able to adapt the techniques shown on
my "Better Histogram" page at www.treeplan.com.

The Better Histogram uses a Column chart type (vertical bars) for a
histogram and a hidden XY (Scatter) chart type for horizontal axis labels.
The hidden XY data series could be used to show a normal curve, and
additional XY data series could be used to show vertical lines.

But I am not familiar with the chart you describe: "vertical columns," which
I would generally associate with a standard histogram showing numerical
values on a horizontal axis and vertical bars whose heights represent
frequencies (which is the kind created by Excel's Histogram tool and
somewhat enhanced by my Better Histogram), and "... and the mean value as
horizontal lines," which would instead be vertical lines using the standard
histogram orientation.

- Mike

www.mikemiddleton.com

+++++++++++++++++++++++++++++

 

[Tushar Mehta]

Hi Phil,

I delayed responding hoping someone else would have an idea of what it
is you are trying to do. It appears that is not the case.

Like Mike, I just don't understand what you are charting.

If you plot 15 data points in a column chart, the x-axis are categories
(such as "Sample 1", "Sample 2", "Sample 3", etc.). It is possible to
calculate the mean for this data set and plot it as a horizontal line.
However, this layout doesn't help any analysis vis-a-vis the
distribution of the data.

In a normal distribution chart, the mean value is that value of x
(along the *x-axis*) where the pdf value (along the y-axis) is the
highest. I cannot understand how one can draw a horizontal line at the
mean value. The same applies to the std. dev. values.

Two references:
Mike Middleton's paper:
ESTIMATING THE DISTRIBUTION OF DEMAND USING BOUNDED SALES DATA
http://www.usfca.edu/~middleton/demand.pdf

and my tutorial
Drawing a Normal curve
http://www.tushar-mehta.com/excel/charts/normal_distribution/index.htm

I imagine you will find more with a google search.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Michael, Tushar -

I think what Phil is doing is plotting 15 randomly distributed values as his 15
vertical columns, then adding horizontal lines for the mean and ± 1 SD for this
population of 15 values. The curve he wants is like Tushar's normal bell curve, only
rotated 90°.

Phil -

Add some blank cells to the data range of your vertical columns, to make some space
along the right of the chart. Use Tushar's Normal Curve technique, which generates
an XY Scatter series, but switch his X and Y values, and adjust the secondary axes
to put this to the right of your columnar data.

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