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?

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

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


"Johnny" wrote in message
...
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?

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.

"Jon Peltier" wrote:

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

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


"Johnny" wrote in message
...
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?

On Thu, 12 Jun 2008, in microsoft.public.excel.charting,
Johnny said:

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/200...art-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.

--
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.

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

http://PeltierTech.com/
_______



"Del Cotter" wrote in message
...
On Thu, 12 Jun 2008, in microsoft.public.excel.charting,
Johnny said:

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/200...art-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.

--
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.

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.

Hi,

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

Cheers
Andy

Del Cotter wrote:
On Thu, 12 Jun 2008, in microsoft.public.excel.charting,
Johnny said:

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/200...art-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.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

On Sun, 15 Jun 2008, in microsoft.public.excel.charting,
Andy Pope said:
Del Cotter wrote:
Johnny said:
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/200...ed-area-chart-
challenge/

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

I've published my simple solution he

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

--
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.

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.

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.

On Sun, 15 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
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
he

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

--
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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Sun, 15 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
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 he

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

--
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.