Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Gulf Stream
 
Posts: n/a
Default How to vary color in an area chart between negative or positive d.

How can I vary the color in an area chart between positive and negative data
points. Using zero as the defining level, anything greater than zero have
one color and anything less than zero having a different color. Thank you
for your help.
  #2   Report Post  
John Mansfield
 
Posts: n/a
Default

Gulf Stream,

Set up two series €“ one for positive values and one for negative values.
Add a one-row space between any data point that crosses zero. For example,
assume your original data is in a single column starting in cell A2. The
data looks like this:

a 2
b 4
c -4
d -3
e 6
f -3
g 5
h 7

Rearrange it to look like this (the title "Positive" is in cell A2 - the
entire range now covers A1 to B13:

Positive Negative
a 2 0
b 4 0

c 0 -4
d 0 -3

e 6 0

f 0 -3

g 5 0
h 7 0

Note the row-space between any row where the values go from positive to
negative or negative to positive. Create the area chart based on the revised
setup.

----
Regards,
John Mansfield
http://www.pdbook.com



"Gulf Stream" wrote:

How can I vary the color in an area chart between positive and negative data
points. Using zero as the defining level, anything greater than zero have
one color and anything less than zero having a different color. Thank you
for your help.

  #3   Report Post  
John Mansfield
 
Posts: n/a
Default

Gulf Stream,

I apologize - I missed something in my example which is causing the chart to
be inaccurate. The X axis values are not consistently spaced in the setup
that I described. I'll continue to work on it.

John Mansfield

"John Mansfield" wrote:

Gulf Stream,

Set up two series €“ one for positive values and one for negative values.
Add a one-row space between any data point that crosses zero. For example,
assume your original data is in a single column starting in cell A2. The
data looks like this:

a 2
b 4
c -4
d -3
e 6
f -3
g 5
h 7

Rearrange it to look like this (the title "Positive" is in cell A2 - the
entire range now covers A1 to B13:

Positive Negative
a 2 0
b 4 0

c 0 -4
d 0 -3

e 6 0

f 0 -3

g 5 0
h 7 0

Note the row-space between any row where the values go from positive to
negative or negative to positive. Create the area chart based on the revised
setup.

----
Regards,
John Mansfield
http://www.pdbook.com



"Gulf Stream" wrote:

How can I vary the color in an area chart between positive and negative data
points. Using zero as the defining level, anything greater than zero have
one color and anything less than zero having a different color. Thank you
for your help.

  #4   Report Post  
Andy Pope
 
Posts: n/a
Default

Hi,

I have created an example.
http://www.andypope.info/ngs/ng33.htm

There's not much in the way of explanation I'm afraid.

The formula are standard trig calcs in order to determine x and y values
for the 2nd data series.

The secondary x axis is changed to a data axis. Use right click, chart
options to get the secondary x axis displayed so you can format it.

Cheers
Andy

Gulf Stream wrote:
How can I vary the color in an area chart between positive and negative data
points. Using zero as the defining level, anything greater than zero have
one color and anything less than zero having a different color. Thank you
for your help.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
add line to a stacked area chart already created Mitch Charts and Charting in Excel 3 April 3rd 23 04:43 PM
How do I shade the area between two lines on a chart? Hart Charts and Charting in Excel 4 April 3rd 23 04:29 PM
Setting custom chart causes plot area to collapse Chiara Charts and Charting in Excel 0 January 20th 05 06:32 PM
Labels on Chart with Negative Value Axis David F. Schrader Charts and Charting in Excel 6 December 17th 04 07:25 PM
Area Chart Formatting Question Avrilon Charts and Charting in Excel 3 December 2nd 04 10:14 PM


All times are GMT +1. The time now is 09:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"