How do I keep zero values from plotting in charts?

[BrianBrand]

I am plotting percentages from a table that will be filled with weekly
values, but not all of the weeks are completed yet, so future weeks are
plotting as zero.

Cells being graphed contain a A1/B1 formula to get a percentage, resulting
in a divide by zero error. I suppressed the error and tried to set the cell
as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1).

I tried setting the Tools > Options > Graphs - Plot Empty Cells As: Not
Plotted, leave gaps. Problem is, even though the value is 'blank' if error =
true, it is no longer a 'blank' cell by Excel's interpretation, so the
'blank' cells are still plotted as zero on the chart.

Has anyone else overcome this problem?

Thank you very much! - Brian

 

[Jon Peltier]

Brian -

The cell isn't empty, it contains a formula. And nothing that a formula
returns is interpreted as an empty cell. You have two options:

1. Change the formula to

=IF(ISERROR(A1/B1),NA(),A1/B1)

This leaves an #N/A error in the cell (which conditional formatting will
hide) but a line chart or XY chart will interpolate past this point.

2. Construct dynamic ranges and use these in the chart series' source data.
This means the chart only has as much data as it can use. Example and links
can be found here:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

 

[ggovier]

Jon

Excel XP 2002...

If plot points 1 and 3 have values, and a formula in the data cell for point
2 resolves to NA(), I get a line from point 1 to 3. I want no line between
point 1 and 3.

The following sounded promising, but "Not Plotted (leave gaps)" and
"Interpolated" both act as interpolated.

Click the chart.

 

[ggovier]

Tools -> Options -> Chart -> Plot empty cells as: Not plotted (leave gaps).
should solve it. Doesn't. using Excel XP 2002

 

[Frank Winston]

This leaves an #N/A error in the cell (which conditional formatting will
hide) but a line chart or XY chart will interpolate past this point.

This post solved an almost exactly the same problem I was having. However,
the above instruction does not seem to be as easy as it sounds.
To get conditional formatting to hide the contents of a cell displaying
"#N/A", this is what I did: (it didn't work)
Selected the cells
clicked conditional formatting
in the appropriate boxes, I entered: "Cell value is", "Equal to" "#N/A"
(without the quotes)
Then I chose formatting with font colour as white so that the white text on
white background is invisible.
Why doesn't this work?

 

[Andy Pope]

Hi,

Try CF, Formula: =ISNA(B3)
changing the cell reference as required.

Cheers
Andy

 

[Frank Winston]

Ah yes that works, thatnks! Although it took me a litle while to work out
that once you hve done one cell you can use the format painter brush to do
them all the same, Duh!

Frank