How do I keep zero values from plotting in charts?

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

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
_______


"BrianBrand" <[email protected]> wrote in message
news:[email protected]...
>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