Line Cart Per Month but no data yet

[KIM W]

EXCEL 2007
I have a simple line chart that takes data from 12 cells contining formulas
giving a calculated value for each month of the year. The Y axis is MONTH.
PROBLEM:
We want the Y axis to show all 12 month names (no problem with that), but we
don't want the line of values to be drawn into the future. I just want the
line to stop if data value = 0 for future months. For future months we
currently get value of zero charted. Of course if there is no formula in the
source data cells, then there is no line drawn, but I am finding that because
there is a formula in the data source cells, something is always being drawn
in the chart.

I tried putting IF = 0 then "" in the formula, but still got liine in chart.
I tried If = 0 OFFSET to a blank cell, but still got something. Problem
seems to be that if there is formula in source data cell, charting insists on
displaying something in chart.

 

[David Biddulph]

In your formula, instead of setting to "", set the result to NA(). Then the
chart will treat the cell as blank.

 

[Jon Peltier]

Change your formula so that it returns NA() for a blank cell. This turns
into #N/A in the worksheet, which is not plotted in a line or XY chart.

- Jon