spreadsheet blues ii: graphing a series that may or may not end

UPDATE BELOW

My latest frustration with Excel? Graphing a data series that may or may not come to an end. As a baseline, consider Figure 1, which shows a simple table and an accompanying chart.

Figure 1: A table of data and accompanying chart

There's not a whole lot to say about this. There are three data series, and three accompanying lines on the chart.

Now, suppose one of the data series ends in 1987. Maybe these are some government statistics and one of them was no longer-calculated as of 1988. Our table and chart might look like this:

Figure 2: One series ends abruptly.

It makes perfect sense. The series ends in 1987, and so does the line that tracks it. Easy peasy, George loves Weezie.

But suppose I build a table without knowing when a series will end. So instead of leaving blanks in the third series starting with 1988, I have a formula that evaluates to a blank. That's what we have in Figure 3.

Figure 3: One series ends, but with a formula to have it end.

The cells in the third data series look blank, but there's a formula there: =""

They formula sets them to be blank, so they appear blank. But because the cell is not truly blank (i.e., it has a formula), Excel charts it as if it's zero.

I would love it if Excel would, for graphing purposes, treat non-numbers as blanks rather than as zeroes.

UPDATE: My actuarial student gave me a solution. I wrote about it here.