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.

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.
>-----Original Message-----
>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.
>.
>

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.

>-----Original Message-----
>No function will ignore zero values. If you are trying
to do linear
>regression, you could use the following array formulas
(assuming x-data
>is in A1:A20 and y-data in B1:B20)
>
>=SLOPE(IF(B1:B20=0,"",B1:B20),IF(A1:A20=0,"",A1:A20))
>=INTERCEPT(IF(B1:B20=0,"",B1:B20),IF(A1:A20=0,"",A1:A20))
>
>These must be array entered (Ctrl-Shift-Enter), and the
trick cannot be
>used with LINEST, since LINEST requres a value in every
referenced cell.
>
>Graphs will ignore #N/A values, so you could change each
cell formula to
>something like
>
>=IF(formula=0,#N/A,formula)
>
>for your graph, and modify the preceding SLOPE and
INTERCEPT formulas to
>
>=SLOPE(IF(ISNA(B1:B20),"",B1:B20),IF(ISNA
(A1:A20),"",A1:A20))
>=INTERCEPT(IF(ISNA(B1:B20),"",B1:B20),IF(ISNA
(A1:A20),"",A1:A20))
>
>Jerry
>
>Richard wrote:
>
>> 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.
>
>.
>

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Whirlwind wrote:
> I'm having a similar problem, except that one of my series
> should not be graphed for observations that are the same
> as another series. I put an #N/A in those cells and they
> are not graphed. The problem is if some values show up,
> followed by some #N/As, and then some other values show up
> in a later observation, Excel interpolates between the
> observations that are not #N/A.
>
> Any ideas? Thanks,
>
> JR
>
>>Graphs will ignore #N/A values, so you could change each
>
> cell formula to
>
>>something like
>>
>>=IF(formula=0,#N/A,formula)
>>
>>Jerry
>>
>>Richard wrote:
>>
>>
>>>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.
>>
>

[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)