No, I guess not. Line and XY charts skip over such a point, while other
chart types treat #N/A as zero. But I guess since you have #N/A for both
category and value for the last point, it gets skipped.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Last night I started a blog post, showing the same technique. I plan to
post it tonight or tomorrow.

Del - didn't you have a problem with the #N/A values when dealing with the
area chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Sat, 14 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:

A column is a discrete element of a series, while the area comprises the
whole series. I suspect that's why area charts don't invert if negative.
And
in fact, the capability is partially broken in 2007 column charts.

The difficulty in this problem is that the area chart goes from positive
to
negative in between points. You would have to convert the plot to a date
scale representation, then work out some interpolation scheme so that the
positive and negative series meet at the proper position along the date
scale axis.

I tried this for a bit yesterday, then gave up and posted that it was
just tricky. But I had an inspiration this morning.

To keep it simple, let's show a data set of just four rows, in this case
consisting of the first four rows of Johnny's original data set. Add a
blank line then, three more rows (one less than the first group, because
it's interpolating, and an extra, extrapolating, row would cause a mess).

Input Year Baseline Above Below
100 01/01/1995 0 100
-150 01/01/1996 0 -150
200 01/01/1997 0 200
250 01/01/1998 0 250

100 27/05/1995 0 0 0
-150 05/06/1996 0 0 0
#N/A #N/A #N/A #N/A #N/A

This works because the Time-scale re-sorts any dates that are out of
order, so you don't have to have any awkward nested if..then formulas,
just your original block of data, and a block one row shorter below it.
The blank row is just for visual effect, and does no harm, but you can't
have another header row, as the Time-scale reads that as 0, or 1 Jan
1900.

In left to right order (assuming the whole block started from the header
row in A1) the formulae for the first row of the second block a

=IF($A2*$A30,NA(),A2)
=IF($A2*$A30,NA(),B2+(B3-B2)*A2/(A2-A3))
=IF($A2*$A30,NA(),0)
=IF($A2*$A30,NA(),0)
=IF($A2*$A30,NA(),0)

"if $A2*$A30" means "if Input for two adjacent rows are of the same
sign", otherwise it sets the Input to zero and the Date to an
interpolated day of the year. You need a different formula if the
baseline is not zero.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.