Fill Positive and Negative Areas under a Scatter Plot Different Co

[adambush4242]

I have a scatter plot connected by lines. I want to be able to fill the
positive area between the line and 0 green and the negative area between the
line and 0 red. Any ideas on how this would be possible? I looked at Jon
Peltier's website but that only allows for one type of shading.

Thanks

Adam Bush

 

[Shane Devenshire]

Hi,

I think you[re going to have problems with this one. I don't think you can
do this on a scatter chart in Excel. You can fake it with an area chart but
there are a number of problems with that approach, not the least of which is
it is not an XY scatter.

 

[Kelly O'Day]

adambush4242

You can get close to what you want by creating 2 series and using colored
error bars.

I have an Excel example at this link.

http://processtrends.com/pg_global_warming.htm#Global_Temperature_Trends

If this example meets your needs, you can download the workbook to see how I
generated the double colored XY chart with error bars. Look at the CRU
sheet.

The chart you really want to make is very straightforward if you add R to
your charting toolkit. Here's a link to a post I have on a panel chart with
colored lines reflecting above - below zero status.
..
http://chartsgraphs.wordpress.com/2009/02/09/r-panel-chart-beats-excel-chart/

If you plan to make advanced charts, you may want to add learn R so that you
make advanced charts in R and paste the results into your Excel workbook.

Kelly

Hi,

I think you[re going to have problems with this one. I don't think you can
do this on a scatter chart in Excel. You can fake it with an area chart
but
there are a number of problems with that approach, not the least of which
is
it is not an XY scatter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I have a scatter plot connected by lines. I want to be able to fill the
positive area between the line and 0 green and the negative area between
the
line and 0 red. Any ideas on how this would be possible? I looked at
Jon
Peltier's website but that only allows for one type of shading.

Thanks

Adam Bush

 

[adambush4242]

Kelly,

Thanks for your response. The error bars are a nice idea, however some of
my points are too spaced out for that to be effective. I appreciate your
help though and if you have any more ideas I'd love to hear them.

Thanks

Adam Bush

adambush4242

You can get close to what you want by creating 2 series and using colored
error bars.

I have an Excel example at this link.

http://processtrends.com/pg_global_warming.htm#Global_Temperature_Trends

If this example meets your needs, you can download the workbook to see how I
generated the double colored XY chart with error bars. Look at the CRU
sheet.

The chart you really want to make is very straightforward if you add R to
your charting toolkit. Here's a link to a post I have on a panel chart with
colored lines reflecting above - below zero status.
..
http://chartsgraphs.wordpress.com/2009/02/09/r-panel-chart-beats-excel-chart/

If you plan to make advanced charts, you may want to add learn R so that you
make advanced charts in R and paste the results into your Excel workbook.

Kelly

Hi,

I think you[re going to have problems with this one. I don't think you can
do this on a scatter chart in Excel. You can fake it with an area chart
but
there are a number of problems with that approach, not the least of which
is
it is not an XY scatter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I have a scatter plot connected by lines. I want to be able to fill the
positive area between the line and 0 green and the negative area between
the
line and 0 red. Any ideas on how this would be possible? I looked at
Jon
Peltier's website but that only allows for one type of shading.

Thanks

Adam Bush

 

[Jon Peltier]

Adam -

Check out Derek's example here:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"(e-mail address removed)"

 

[adambush4242]

Jon,

I am having a lot of trouble with Derrick's example. He is very vague and
unclear in how he came to his solution. Can you possibly explain his
reasoning better?

Thanks

Adam Bush

 

[Jon Peltier]

I thought his example was rather clear and concise, but then, he and I both
have lived in the Excel charting system.

What parts of the analysis are you having problems with?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"(e-mail address removed)"

 

[adambush4242]

I guess the most confusing part is what do I actually graph after doing the
calculations? For the calculations, do i put the four formulas in A-D, then
do I drag them down? Should I have the same number of formulas as data
points?

Thanks

Adam Bush

 

[Jon Peltier]

Isn't there a sample file linked from Derek's article?

Working from memory here, so caveat emptor.

I think you need one fewer cell with formulas than cells with data, since
the formulas interpolate between the data points. Dragging them down or
copying/pasting should be fine, since the relative references will change to
include the next pair of points. The series data range stretches to include
both the data and formulas.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"(e-mail address removed)"

 

[adambush4242]

I checked his website and I don't see any attached files. So what data to I
graph? Any of the original data? Columns A-D, or just some of them?

Thanks

Adam Bush

 

[Jon Peltier]

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

In the original table, Derek has the original data in columns A (values) and
B (dates). Columns C and D have calculated data for Above and Below. The
actual points are in the first four rows (above the blank line), while the
interpolated data is in the last three (below the blank line). You need to
plot columns B-C-D, and all rows. Before doing so, clear the cell that says
"Date", to help Excel parse the data range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"(e-mail address removed)"

 

[adambush4242]

My graph is plotting the NA values and I'm not sure why.

Adam Bush

 

[Jon Peltier]

In an area chart, they should be plotted as zeros.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"(e-mail address removed)"