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" <[email protected]> wrote in message
news:%[email protected]...
>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

[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 wrote:
> 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
>
>

[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



"Jay" <[email protected]> wrote in message
news:%[email protected]...
> 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 wrote:
>> 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
>>