Ignor zero values

[Andrew B]

This is a form of a common query, but I haven't been able
to nail down an answer...

I have a simple graph, the data series are formulae
calculating data from another source.
The catch is, I don't want the graph to plot zero values!

Tools>Options>Graphs>Plot Empty Cells does not work
because there are formulae in the cells.
Suggestions to utilise error symbols don't work because
I subsequently reference the data series for further
calculations.

Any thoughts anyone? (Running Excel 2002 SP3)

 

[Jerry W. Lewis]

You can use array formulas (array entered with Ctrl-Shift-Enter) to
strip the errors out of the data for subsequent processing, as in
=AVERAGE(IF(ISNUMBER(range),range))
or you can delete the formulas that are returning zero.

For dynamic data, you could write a worksheet change event that would
delete or restore formulas as needed, but that is a non-trival exercise.

Jerry

 

[Jon Peltier]

In this kind of situation, I have two data ranges with similar formulas.
One returns values or something that doesn't mess up the chart (#N/A),
while the other returns values or something that doesn't mess up further
calculations. Chart the one set of values, make your calculations from
the other, and hide both. All that has to show in the report is the
chart and the computed results.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tushar Mehta]

In the formula where you get a zero, replace it with NA().

This will interpolate the line for line and scatter charts and leave a
hole for a column/bar chart. It won't work for an area chart or a pie
chart where NA() is treated as zero.

If the #N/A is aesthetically unappealing or messes up downstream
computations, create a second data set that looks like
=if(actual-data=0,NA(),actual-data)
Now, plot this 2nd data set.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions