Color of bars in chart to be determined by a condition

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I'd like the individual colors of the bars in a hi-lo-
close chart to be determined by a condition in the
spreadsheet. For instance if the close is above a moving
average then The bars should be coloured yellow. If below
the moving average the bars should be coloured blue,. Can
this be achieved.
Thank you
 
Ben -

I think you need to dispense with the OHLC chart and build your own
chart. The columns you need are:

A. Date
B. High
C. Low
D. Bottom
E. Yellow
F. Blue
G. Open
H. Close
I. MovAvg

In cell D2 (allowing row 1 to hold column header captions), you put this
formula:

=MIN(G2,H2)

in cell E2:

=IF(H2>=I2,ABS(G2-H2),NA())

in cell F2:

=IF(H2<I2,ABS(G2-H2),NA())

Fill these formulas down as far as the actual data reaches.

Now it's time to construct the chart. It's going to be messy. Column A
is the time across the X axis. Columns B and C hold the Hi-Low data,
which will be plotted as line chart series without lines or markers, and
connected with Hi-Low lines. Columns D-F will be stacked column chart
series. The Bottom series will be invisible (no border, no fill),
Yellow and Blue will be colored appropriately, and only one or the other
will appear at any time.

Select the data in columns A to F and make a Line chart. Right click
the Bottom series, select Chart Type from the pop up menu, and choose
any of the Column types. Right click the Yellow series, select Chart
Type from the pop up menu, and choose the Stacked Column type. Select
the Blue series and press the F4 key to repeat this action.

Double click the Bottom series, and on the Patterns tab select None for
Border and for Fill. Format the Yellow and Blue series to suit.

Double click on one of the line series, and on the Patterns tab, select
None for Marker and for Line, then on the Options tab, select High-Low
Lines. Select the other line series, and press the F4 key.

If you followed this, and I didn't leave out any steps, you've just made
a neat little conditional floating column chart.

- Jon
 
Back
Top