Dynamic Chart

[Mark Allen]

I have a dynamic chart working but when the future vale = 0 or if it has a
formula in the cell the chart reads it as 0.

Is there any way around this ??

 

[Jon Peltier]

The formula returns "", which Excel interprets as text, and assigns a value
of zero. Change this to NA(), which leaves an error in the cell (#N/A), but
which is not plotted in a line or XY chart.

- Jon

 

[Andy Pope]

Hi,

If you have a formula it needs to return #N/A instead of zero.
example,

=IF( <test> , <value> , NA() )

Cheers
Andy

 

[Mark Allen]

nope it still see's it as something there so the dynamic chart does not work...

this is the formula I am using:

=IF(SUMIF(F:G,L29,G:G)=0,NA(),SUMIF(F:G,L29,G:G))

Any other ideas to try ??

Mark

 

[Jon Peltier]

Probably should be

=IF(SUMIF(F:F,L29,G:G)=0,NA(),SUMIF(F:F,L29,G:G))

but I get the same answer either way. What's in column G? If my sumif
resolves to zero, the formula results in #N/A.

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