Offset Dynamic Charts with Conditional Formatting - NA() Issue

[Cantonalives]

Hi,
I have been using Offset for some time to create dynamic charts, which has been very successful.

It occurred to me this morning on a different requirement that on a line chart it would be nice to have red lines below zero and blue above zero. This in itself I created relatively successfully using different series for 'normal' and then 'below'. However the flaw is that if the line is red, goes above zero and then dips back under, the chart does not change the colour for the second dip?

One issue as part of this is that if an offset range has the first value as a #N/A, errors are received. A zero would be plotted as zero as would a blank - any suggestions appreciated?

dummy data now attached although it may have changed colours... You will get the idea..



Thanks,


-CL

 

[Cantonalives]

Hi,
Bumping in the hope someone has some ideas or suggestions..


Thanks,


-CL

 

[NARAYANK991]

Hi ,

The problem is that a line which crosses the X-axis has to have its positive portion in one color and the negative portion in a different color.

See if this link helps :

http://peltiertech.com/area-chart-invert-if-negative/

Narayan

 

[Cantonalives]

Not quite and think I need to try the VB route - Thanks anyway.

-CL