Excel 2007 : Conditional Formatting with Icons Sets and 4 scenarios

[atheisen]

I am having trouble applying conditional formatting using the icon set (red, yellow, and green), when I have four different scenarios that could result in those three colors. When I enter the month's ending asset number, it should display the icon based on the criterion below.

If assets are >= the Jan Budget and < the Jan High, then it should display green.
If assets are >= the Jan Low and < the Jan Budget, then it should display yellow.
If assets are < the Jan Low, then it should display red.
If assets are > the Jan High, then it should display red.

I have attached a sample spreadsheet, the formatting is working for the first three criterion listed above, but not for the fourth.

[K m]

These are the three conditional format conditions for cell B2

=AND(B2>=B18,B2<B19) {Format Green}

=AND(B2<B17,B2<B18) {Format Yellow}

=OR(B2<B17,B2>B19) {Format Red}

Copy the formula down

[atheisen]

How can I transfer those equations into the conditional formatting window pictured here:

ohi20120720122327-000.jpg

[K m]

Enter the formulas in the conditional format in "Cell Value Is"
section

[atheisen]

I am sorry, I must be really dense. I am not getting any combination of formulas to yield the correct result (red) when B2 is over the Jan high figure, but at the same time apply the other rules, so that B2 changes to the correct color is each available scenario. Can you enter the correct formulas (rules) into the spreadsheet from my original post?

[K m]

See Attached


FINANCIAL DASHBOARD-1.xlsx

If this solves your problem change the subject to [SOLVED] and click on the star at the bottom of this message

[atheisen]

I do not want any shading on the cell, the only things that should change colors is the icon. That is why I am not seeing a way to enter three formulas using the icon set.

[K m]

It appears you can not do what you want using the icon sets as it lets you define two conditions and a third condition if neither other condition is matched