Chart plots #VALUE as 0, can this behavior be avoided?

[THOMAS CONLON]

I have an X-Y scatterplot. In some of the cells in the range for Y values,
the result is #VALUE. However, the chart plots this as if it were "0".
This seems dangerously wrong to me, seems like it should be plotted as if
that cell were blank. It is very misleading in the chart to see those
values plotted as "0". "0" is a possible true and correct value in this
particular data, but those cells do not have that value, so it should not be
plotted as if it were that value.

At any rate, is there any way around this [bad] behavior?

Thank you. Tom Conlon

 

[Bernard Liengme]

Your y-values must be coming from a formula.
Let say if is =UrForm
Replace by =IF(ISERROR(UrForm),NA(),UrForm)
Now in place of #VALUE! you will get #N/A which the charting engine will
ignore
best wishes

 

[THOMAS CONLON]

Yes, that does the trick. Thanks for the help!!

This really does seem like a BUG to me tho. On the sheet, if i add, say,
10, to that value, the result is not 0+10=10, but it is #VALUE. The chart
should not treat this as if it were 0! I suppose Microsoft would never fix
a thing like this, though. I guess no economic return to them to do so.

Thanks,
tom

Your y-values must be coming from a formula.
Let say if is =UrForm
Replace by =IF(ISERROR(UrForm),NA(),UrForm)
Now in place of #VALUE! you will get #N/A which the charting engine will
ignore
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

I have an X-Y scatterplot. In some of the cells in the range for Y
values, the result is #VALUE. However, the chart plots this as if it were
"0". This seems dangerously wrong to me, seems like it should be plotted
as if that cell were blank. It is very misleading in the chart to see
those values plotted as "0". "0" is a possible true and correct value in
this particular data, but those cells do not have that value, so it should
not be plotted as if it were that value.

At any rate, is there any way around this [bad] behavior?

Thank you. Tom Conlon