You can use an IF formula with NA(). For example:
The chart will interpolate over the #N/A errors. To hide these errors,
you can use conditional formatting.
> I have a chart based on cells that are the result of a
> calculation. The cells show empty if the data used for
> the calculation is not yet available (future data). As a
> result, as my chart shows the whole year, the line drops
> and remains to zero after the last available set of data.
> Excel will let me prevent an empty cell of being plotted
> but since there is a fornmula is the cell, it is not
> considered empty, and therefore displayed.
> How can I prevent these cells to be plotted on the chart?
Excel FAQ, Tips & Book List