Excel - Null values and if statements 2

[maca9]

Hi


I have a complex formula that is dependent on the date. Basically if the date is in the future the formula will return "". When the period exists a figure will be there.

My problem is graphing this data because it get graphed as zero when it is returning "".

I have ticked the box saying plot empty cells as - not plotted , leave gaps.


But it is treating the "" as a zero.


How can I make it see these cells are empty?

Thanks in advance.

 

[MeGustaXL]

Hi maca9,

This is because the cells which result in "" contain formulas, and are therefore not blank! You can prove this to yourself by selecting a few of the offending cells and doing Edit>Clear>All. Your chart'll now show blanks where once it went to zero.

When I played with this just now, Clearing All of the so-called blank cells was a bit flakey, in that only the last 6 or 7 points were skipped; the others were still plotted - don't know why, I'm afraid to say.

Chris

Varium et mutabile semper Excel

 

[xlbo]

To not plot those cells, instead of using
=IF(test,true,"&quot
use
=IF(test,true,#N/A)

charts don't plot #N/A so it should work. You must use #N/A rather than "#N/A" however as you need to return the error value rather than a string

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MeGustaXL]

You can also use
=IF(test,true,NA())

Just to confuse yourself even more!




Chris

Varium et mutabile semper Excel