Area Chart with Shaded Areas Diff. Color

[Johnny]

I want to create a chart where the areas under the baseline are a different
color than the areas above the baseline. Is that possible?

 

[Jon Peltier]

You could split the series into above-baseline and below-baseline series.

- Jon

 

[Johnny]

I did split the series but the shading for the numbers below the baseline
bleed above the baseline when the next number in the series falls above the
baseline. Hope that made sense. For example, here's my data:

Input Year Baseline Above Below
100 1995 0 100
-150 1996 0 -150
200 1997 0 200
250 1998 0 250
-300 1999 0 -300
250 2000 0 250
200 2001 0 200
100 2002 0 100
-25 2003 0 0
-50 2004 0 -50
-100 2005 0 -100
-150 2006 0 -150
-200 2007 0 -200
-250 2008 0 -250
-300 2009 0 -300
0 2010 0 0


I created if statements to split the series into those that feel above the
baseline number and those that fell below the baseline number.

 

[Del Cotter]

I want to create a chart where the areas under the baseline are a different
color than the areas above the baseline. Is that possible?

It's possible, but *very* tricky in the details. Jon did a similar
exercise a little while ago in his blog:

http://peltiertech.com/WordPress/2008/05/08/stacked-area-chart-challenge/

Are you sure you couldn't just use a column graph instead? Excel Column
chart type has the facility that's strangely lacking in its Area chart
type, the ability to change color when the value is negative.

 

[Jon Peltier]

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've done this, but didn't have the energy to address it for
this thread. Although is sounds like a good blog post....

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Del Cotter]

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 are:

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

"if $A2*$A3>0" 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.

 

[Andy Pope]

Hi,

This should give you a jump start.
http://www.andypope.info/charts/conditionalline2.htm

Cheers
Andy

 

[Del Cotter]

This should give you a jump start.
http://www.andypope.info/charts/conditionalline2.htm

I've published my simple solution here:

http://i-ocean.blogspot.com/
2008/06/excel-area-chart-with-colour-invert-if.html

 

[Jon Peltier]

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]

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

 

[Del Cotter]

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?

Not with this one. I've just tried putting some spoiler values in, and
it looks pretty robust against interference. I did have a problem with
the interrupted step chart a couple of weeks ago, because the values I
put in became a bridge that caused the line to plunge to zero and back
up again like a bar chart. But this one's okay; you can see the results
here:

http://i-ocean.blogspot.com/
2008/06/excel-area-chart-with-colour-invert-if.html

 

[Jon Peltier]

I actually tried your data with the #N/A values, saw that it worked fine
(perhaps because both X and Y were #N/A), and posted a self-rebuttal.
Outlook Express choked on this, crashing after I hit Send.

I also saw that you'd blogged about it first, and I'm wishing I'd spent time
on this topic instead of the one I actually posted today. Not that we were
having a race!

- Jon