Formula'd cells charting as zero values

[Jay]

I have a very simple series, on which a line chart is based. The series
contains the following formula:

=IF(I6<>"",H26+I6,"")

Therefore the cells in the range are blank if the source cell (row I) is
empty.

However, all cells with the above formula are being plotted as a zero
value in the line chart?

Can anyone advise how to prevent this happening while retaining the
formula? I want NO value to be plotted instead of a zero.

Many thanks,

Jay

 

[Peo Sjoblom]

Use an error instead

=IF(I6<>"",H26+I6,NA())


will work, if the error annoys you from a design point of view you could
hide it using
conditional formatting and white fonts


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

 

[Jay]

I'd rather not mess about hiding errors with c/formatting.

So, am I right in thinking that my problem is that the empty text string
("") charts as a zero for some reason. Why is this?

From just trying things this morning I see that any non-numeric data is
charted as zero, and the only way (from my limited messing about) to not
chart the cell as a zero is to have either an error or a truly empty
cell (not just an empty string).

Now I can see that the truly empty cell option isn't viable, considering
the cells need to contain a formula. Are there any other ways to not
chart a zero, without conditionally formatting the error.

Cheers

Jason

 

[Peo Sjoblom]

It's not a conditional formatting error, you misunderstood. Try the formula
I gave you and you'll see that it will work
as if the cell was empty. The reason I mentioned conditional formatting is
that the formula will return #N/A! and you can hide that result by using
conditional formatting. But it is the only way to dupe Excel into charting
the cell as null


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com