RedYellowGreen Color Banding in Chart Plot Area
 

I want to instruct sections of the chart plot area to display red, yellow and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

I've seen several ways to do this, but the one I prefer is to add 3
additional series to the chart. For example.

Green = 1
0.9 <= Yellow < 1
Red < .9

For the green series, do this

=IF(A1=1,A1,NA())

Yellow: IF(AND(A1=.9,A1<1),A1,NA())
Red: IF(A1 < .9,A1,NA())

Then I add these three series to the chart and change the color of the
series but keep the shape the same.

I'm sure the Chart Experts have other ways, but this is one option.
--
HTH,
Barb Reinhardt




"DOUG ECKERT" wrote:

I want to instruct sections of the chart plot area to display red, yellow and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Barb: I am trying to apply color banding to the plot area of the chart and
not the underlying spreadsheet. So, where would I enter your instructions?
Is this entered in the VBA code - (in which case, I am lost, probably)? The
finished chart would show that everything in the bottom band is red, the
middle band is yellow and the top band is green.

(Thanks).

DOUG ECKERT

"Barb Reinhardt" wrote:

I've seen several ways to do this, but the one I prefer is to add 3
additional series to the chart. For example.

Green = 1
0.9 <= Yellow < 1
Red < .9

For the green series, do this

=IF(A1=1,A1,NA())

Yellow: IF(AND(A1=.9,A1<1),A1,NA())
Red: IF(A1 < .9,A1,NA())

Then I add these three series to the chart and change the color of the
series but keep the shape the same.

I'm sure the Chart Experts have other ways, but this is one option.
--
HTH,
Barb Reinhardt




"DOUG ECKERT" wrote:

I want to instruct sections of the chart plot area to display red, yellow and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Nope, this is not banded, but shows the color code for the specific point.
I've seen something that does the bandking, but haven't tried it in a long
time so will leave that to the experts.
--
HTH,
Barb Reinhardt




"DOUG ECKERT" wrote:

Barb: I am trying to apply color banding to the plot area of the chart and
not the underlying spreadsheet. So, where would I enter your instructions?
Is this entered in the VBA code - (in which case, I am lost, probably)? The
finished chart would show that everything in the bottom band is red, the
middle band is yellow and the top band is green.

(Thanks).

DOUG ECKERT

"Barb Reinhardt" wrote:

I've seen several ways to do this, but the one I prefer is to add 3
additional series to the chart. For example.

Green = 1
0.9 <= Yellow < 1
Red < .9

For the green series, do this

=IF(A1=1,A1,NA())

Yellow: IF(AND(A1=.9,A1<1),A1,NA())
Red: IF(A1 < .9,A1,NA())

Then I add these three series to the chart and change the color of the
series but keep the shape the same.

I'm sure the Chart Experts have other ways, but this is one option.
--
HTH,
Barb Reinhardt




"DOUG ECKERT" wrote:

I want to instruct sections of the chart plot area to display red, yellow and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Doug -

Try this:

http://peltiertech.com/Excel/ChartsH...orizBands.html

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


"DOUG ECKERT" wrote in message
...
I want to instruct sections of the chart plot area to display red, yellow
and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and
hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Jon: That got me a little bit closer. But, the color bands kept cancelling
each other out, i.e., red saved over yellow, yellow saved over green and so
on. The little chart symbol in the upper left-hand side of the page you sent
me is what I am trying to accomplish...

DOUG

"Jon Peltier" wrote:

Doug -

Try this:

http://peltiertech.com/Excel/ChartsH...orizBands.html

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


"DOUG ECKERT" wrote in message
...
I want to instruct sections of the chart plot area to display red, yellow
and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and
hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Did you use stacked areas for the background series? Regular areas will
cause them to block each other.

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


"DOUG ECKERT" wrote in message
...
Jon: That got me a little bit closer. But, the color bands kept
cancelling
each other out, i.e., red saved over yellow, yellow saved over green and
so
on. The little chart symbol in the upper left-hand side of the page you
sent
me is what I am trying to accomplish...

DOUG

"Jon Peltier" wrote:

Doug -

Try this:

http://peltiertech.com/Excel/ChartsH...orizBands.html

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


"DOUG ECKERT" wrote in message
...
I want to instruct sections of the chart plot area to display red,
yellow
and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and
hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Jon: Yes. In the spreadsheet, I added the threshold targets. Then, I added
the secondary X and Y axes, selected each of the resulting lines and
formatted them as a stacked area chart (with Pattern One, i.e., the first
stacked area chart on the left-hand side of the Chart Option drawings). I
did notice the secondary Y-axis scale did not match the primary one - (it
seemed lopsided). Each stacked area only formatted the area above it and it
only used the last color chosen, i.e., the color for that one threshold -
either green or yellow or red. I am trying to make a chart which looks more
or less like your logo.

DOUG

"Jon Peltier" wrote:

Did you use stacked areas for the background series? Regular areas will
cause them to block each other.

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


"DOUG ECKERT" wrote in message
...
Jon: That got me a little bit closer. But, the color bands kept
cancelling
each other out, i.e., red saved over yellow, yellow saved over green and
so
on. The little chart symbol in the upper left-hand side of the page you
sent
me is what I am trying to accomplish...

DOUG

"Jon Peltier" wrote:

Doug -

Try this:

http://peltiertech.com/Excel/ChartsH...orizBands.html

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


"DOUG ECKERT" wrote in message
...
I want to instruct sections of the chart plot area to display red,
yellow
and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and
hard-to-use.

'Suggestions?

DOUG

RedYellowGreen Color Banding in Chart Plot Area
 

Jon: I did it - and you gave me the key!

Thank you so much.

DOUG

PS, I based the RYG bands on the Primary Axis and changed them to Area
Charts. They key was to set the Red band on the Yellow cut point, the Yellow
band on the Green cut point and to set the Green series "goal" to 100 percent.

"DOUG ECKERT" wrote:

Jon: Yes. In the spreadsheet, I added the threshold targets. Then, I added
the secondary X and Y axes, selected each of the resulting lines and
formatted them as a stacked area chart (with Pattern One, i.e., the first
stacked area chart on the left-hand side of the Chart Option drawings). I
did notice the secondary Y-axis scale did not match the primary one - (it
seemed lopsided). Each stacked area only formatted the area above it and it
only used the last color chosen, i.e., the color for that one threshold -
either green or yellow or red. I am trying to make a chart which looks more
or less like your logo.

DOUG

"Jon Peltier" wrote:

Did you use stacked areas for the background series? Regular areas will
cause them to block each other.

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


"DOUG ECKERT" wrote in message
...
Jon: That got me a little bit closer. But, the color bands kept
cancelling
each other out, i.e., red saved over yellow, yellow saved over green and
so
on. The little chart symbol in the upper left-hand side of the page you
sent
me is what I am trying to accomplish...

DOUG

"Jon Peltier" wrote:

Doug -

Try this:

http://peltiertech.com/Excel/ChartsH...orizBands.html

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


"DOUG ECKERT" wrote in message
...
I want to instruct sections of the chart plot area to display red,
yellow
and
green bands based on stops, or thresholds. I have tried Format Plot
AreaGradient FillGradient Stops, but it was very imprecise and
hard-to-use.

'Suggestions?

DOUG