spreadsheed solution

Last month I wrote a post about a problem I was having in graphing with spreadsheets. Specifically, I have a file with series that may or may not come to an end at any point. If the series has ended then the formula for the entry in the series is evaluated as a blank. I want Excel, in graphing that series to show no value for blank cells. But if a cell is calculated to be blank, Excel treats it as a zero. That post was here.

One of my actuarial students had the solution. Instead of making the entries in the series calculate to blanks, have them calculate as errors. That, for some reason, makes Excel evaluate them as nonvalues.

In the illustration above, the first data series grows linearly. The second one is flat. But, as can be seen in the illustration, it is forced (by formula) to be an error when the first series is greater than 10.

This is not a perfect solution; it would be prettier to have the table display blanks. I guess that can be accomplished with conditional formatting. On the other hand, it may be more clear to display the error value.