Auto-formating charts

[edmundo1971]

Tricky one this one (I think).

To begin with I have a simple Column/Line chart - Column showing sales, Line showing target.

I am trying to auto-format the columns so that those above target show green and those below target show red. The simplest solution I've found so far is to use a Clustered Column Chart, link the Chart to two ranges (one showing just those values above target and one showing just below target), format them either green or red as appropriate and then to increase the overlap to 100 (see attached example Chart 1).

However, I want to use a Stacked Column Chart so that I can split the sales by category AND show remaining stock whilst at the same time showing the appropriate values in a data table in the chart and having the auto-format described above.

Chart 2 (which I've manually formatted) hopefully shows what I'm trying to achieve using Chart 1 as a reference point. However, I'm baffled as to how I would go about achieving it, if indeed it is possible.

Thanks as always for your assistance.

Ed

[DBY]

Hello Ed
I think you would need to create two series of data for your chart, one for the above figures and one for below, utilizing the NA() function. Set each one to the desired colour. See the attached example.
Perhaps this is what you are looking for. This has been done in Excel 2010, so I hope the formatting transfers okay to 2003, if that's what you are using.
DBY

[edmundo1971]

DBY

Many, many thanks!!!

All I need to sort out now is the issue of the data table - If I can just somehow get it to show just one set of values (i.e. just apples as opposed to apples (above) and apples (below))...

Thanks

Ed