RedYellowGreen Color Banding in Chart Plot Area

[DOUG ECKERT]

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
Area>Gradient Fill>Gradient Stops, but it was very imprecise and hard-to-use.

'Suggestions?

DOUG

 

[Barb Reinhardt]

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.

 

[DOUG ECKERT]

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]

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.

 

[Jon Peltier]

Doug -

Try this:

http://peltiertech.com/Excel/ChartsHowTo/HorizBands.html

- Jon

 

[DOUG ECKERT]

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]

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

- Jon

 

[DOUG ECKERT]

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

 

[DOUG ECKERT]

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.