ignoring zero values in excel charts

[Richard]

I am trying to create an x/y scatter plot from an excel
sheet where the cells in the data range contain formulae
which sometimes return a zero value - when this occurs I
want the chart to ignore the value as though the cell was
empty but I am having no luck in doing this. It would
also be nice if the LINEST function could be made to
ignore zero values as well.

 

[Joe]

Having the same problem. It's okay if there is absolutely
nothing in the cell the chart is pulling from, but if the
actual value is zero, it dips down and looks very
distracting.
I have tried various different ways to get around but none
have been successful as of yet. The way I am currently
trying is I want to put a user-defined formula in the
source data values that will return an array. But any
time I try to enter the function it tells me I am doing it
wrong...not that it doesn't allow you to put function in
charts.
Don't know if this makes sense or not, but if it does in
any way, let me know.

 

[Joe]

Jerry, I appreciate that! All I needed to know was that
Graphs will ignore #N/A values. Never knew that before.
Thanks for the tip!

Joe B.

 

[Jon Peltier]

Tushar Mehta has a routine that blanks out these N/A values, which
aren't plotted at the ends of a series, but are interpolated over
between valid points. I forget the precise name, but it has the words
Chart and N/A in it. Look at the list on the left side of his web site
(http://tushar-mehta.com).

- Jon

 

[thepcer]

I found I much easier way!

Plot a new series (hide if you like) with this example command:

if(iserror(value(A1)),na(),A1)

​