How do I make a graph ignore zero's in Excel 98

Hi,

I set up a chart that graphs changes in quantity over time.

It's source data comes from multiple different places in my
spreadsheet, so, to make it easy to graph, I use formulas to set it
out nicely.. namely, two rows, the top with date and the bottom with
quantity.

Problem is, that everyday I have to copy the quantity grabbing formula
over to the next column.

If I leave the formula pre-copied right across, it skews my trend line
and makes my graph look terrible because it dives down to zero.

If I don't copy the formula, I miss the latest set of data.

Essentially, I just want it to consider zero's as skipped values but I
can't seem to make it happen.

Any advice would be very much appreciated.

Thanks

Have your formulas return #N/A instead of zero.

Jerry

Tino wrote:

Hi,

I set up a chart that graphs changes in quantity over time.

It's source data comes from multiple different places in my
spreadsheet, so, to make it easy to graph, I use formulas to set it
out nicely.. namely, two rows, the top with date and the bottom with
quantity.

Problem is, that everyday I have to copy the quantity grabbing formula
over to the next column.

If I leave the formula pre-copied right across, it skews my trend line
and makes my graph look terrible because it dives down to zero.

If I don't copy the formula, I miss the latest set of data.

Essentially, I just want it to consider zero's as skipped values but I
can't seem to make it happen.

Any advice would be very much appreciated.

Thanks

There will still be a problem calculating a trendline even though you
can't see the zero-value columns.

Your suggestion to make a dynamic chart that uses only the non zero
range is the way to go. I have some examples and links regarding
dynamic charts on my web site:

http://www.geocities.com/jonpeltier/.../Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

FatherGuido wrote:
On Thu, 18 Sep 2003 04:47:24 GMT, (Tino) wrote:


Hi,

I set up a chart that graphs changes in quantity over time.

It's source data comes from multiple different places in my
spreadsheet, so, to make it easy to graph, I use formulas to set it
out nicely.. namely, two rows, the top with date and the bottom with
quantity.

Problem is, that everyday I have to copy the quantity grabbing formula
over to the next column.

If I leave the formula pre-copied right across, it skews my trend line
and makes my graph look terrible because it dives down to zero.

If I don't copy the formula, I miss the latest set of data.

Essentially, I just want it to consider zero's as skipped values but I
can't seem to make it happen.

Any advice would be very much appreciated.

Thanks


You need to select the date range to end at the end of the data, or use
an array formula to build the chart data up to the day before the "0".
There is no way to turn it off. Line charts cause this problem, but if
you used a column chart this wouldn't be a problem. You can have the
columns join each other which gives you a staircase type of line chart.

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good