ignoring zero values in excel charts

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.

That is the advertised behavior of #N/A. Excel does not have a true
missing value, so your only option with intermediate missing values is
to delete the formulas from those cells. You could automate the process
with an event-linked macro.

Complaints about the need for a true missing value predate my 6 years in
these newsgroups, so I wouldn't hold my breath about MS addressing this
shortcoming any time soon.

Jerry

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.

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.

Many thanks for the posts. I've tried Tushar's Add-in,
but it doesn't appear to be working properly. It's a
little quirky really...deleting line segments it shouldn't
have and resetting formats to Automatic (maybe only when I
turn it off). I'll experiment with it some more, but I'm
nearly at the point of giving up in favor of illustrating
the data in another way.

Thanks again,

JR

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