Graph help

[excel_noob82]

Hi all,


I have a graph which shows the projected sales figure (column chart) and budget (line graph).


Is there a way i can have the column change to be red if it is under the budget/green if it is over it?


I know i can do it manually by formatting the data point, just thought you guys may know a way to do it automatically?


Any help would be greatly appreciated!

 

[Luke M]

The trick is using an extra data series. Basically, you'll have 2 series. One for red, the other for green. The red series will look at your actual data and do something like:

=IF(Actual<Goal,Actual,NA())


and the green series will be:

=IF(Actual>=Goal,Actual,NA())


Thus, each series will only plot of the columns that it should be colored for. The NA error tells the chart not to plot a point for that particular data point.


Now, to get things to line up, depending on what chart type you are using, you can either do a stacked chart (no extra change needed) or a side-by-side column chart, but set the overlap to 100%


Further reading of conditional charting:

http://peltiertech.com/WordPress/conditional-formatting-of-excel-charts/