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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

THOMAS CONLON - 20 Sep 2006 17:22 GMT

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

Reply to this Message

Bernard Liengme - 20 Sep 2006 17:31 GMT

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

Signature

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".
[quoted text clipped - 7 lines]
>
> Thank you. Tom Conlon

Reply to this Message

THOMAS CONLON - 20 Sep 2006 21:57 GMT

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
[quoted text clipped - 13 lines]
>>
>> Thank you. Tom Conlon

Reply to this Message