add high/low/close lines to a stacked column chart

[Tammy]

I need to add high/low/close lines to a stacked column chart. The
high/low/close values are unique, and each one corresponds with one of
the stacked bars. they should be on the same y-axis

Or I can add points next to each column of my stacked column chart and
then add custom error bars to those points. Either way will work, but
I can't figure out how to do either of these options.

Thanks

 

[Jon Peltier]

Do you have a bunch of stacked bars, and you're showing the HLC for each?

- Jon

 

[Tammy]

Yes, that's correct. But the HLC values are not the values of the
stacked bars, I have unique HLC values for each stacked bar that I
want to show for each, for a comparison.
Tammy

 

[Jon Peltier]

Not sure exactly how it should look, but I'll take a stab.

Assuming something like this for your stacked columns, where A-D are four
series, and a-g are the category labels.

A B C D
a 3 3 6 5
b 3 3 4 5
c 6 6 3 3
d 3 6 4 3
e 4 4 4 3
f 3 3 4 4
g 5 4 6 3

There's the stacked column chart. Suppose the HLC looks like this:

H L C Up Down
1.33 17.49 13.33 15.74 1.74 2.42
2.33 10.69 6.92 8.36 2.33 1.44
3.33 17.47 3.28 15.40 2.07 12.12
4.33 14.64 5.36 12.84 1.80 7.47
5.33 14.39 6.66 7.14 7.25 0.48
6.33 13.77 11.95 13.03 0.74 1.08
7.33 15.29 3.05 5.02 10.28 1.96

where Up is high minus close and Down is close minus low, for the error bar
values. The 1.33, 2.33, etc are the category values for the Close series,
which will be added as an XY series. Values of 1, 2, 3 would coincide
exactly with the columns, so I've added 0.33 to offset them to the right.
Copy the 1.33, 2.33 column and the Close column (hold Ctrl to select the
second area), select the chart, and use paste special from the Edit menu to
add the data as a new series, categories in first row. It's added as a new
stacked column.

Select the new series, go to Chart menu > Chart Type, and change this to an
XY type, markers without lines. Double click this new series, and on the
Axis tab, choose Primary, then hit Enter. Now you see how the 1.33, 2.33,
etc., lines up. Double click the series again, go to the Y error bars tab.
Select Custom, click in the Custom (+) box and select the Up column with the
mouse, then click in the Custom (-) box and select the Down column. Format
the error bars to hide the end caps (if desired).

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

 

[Tammy]

that is exactly what I was looking for. Thank you very much Jon!

 

[Jon Peltier]

Good guess, then.

- Jon