Conditional Formatting with Percentages - only 2 of 3 working

[Roady]

Hi:

I am trying to imput the following Conditional Formatting relating to costing:
2 columns -
Column A has a costing goal
Column B has the costing actual
I want the conditional formatting to reflect how close the costing actual
comes to the goal

Green- if cell B value is < or = column A
Yellow- if cell B value is greater than A but less than A+3%
Red- if cell B value is greater than A+3%

I have tried entering the appropriate conditions but it will only choose
Green or Red. Yellow won't work.

Can you help? Thanks!

 

[Max]

Try it in this manner ..

Assume data in cols A and B, from row2 down
and you want to conditionally format only col B (CostActual)
based on comparison with col A (CostGoal)

Steps (in xl2003):
Select col B (with B1 active), then apply CF using Formula Is
Condition 1: =AND(COUNT($A1:$B1)=2,$B1<=$A1)
Format Green fill/white font
Condition 2: =AND(COUNT($A1:$B1)=2,$B1>$A1,$B1<$A1*1.03)
Format Yellow fill
Condition 3: =AND(COUNT($A1:$B1)=2,$B1>=$A1*1.03)
Format Red fill/white font
Ok out

Illustration for the above (with CF dialog screenshot):
http://www.freefilehosting.net/download/3ablf
Conditional Formatting with Percentages.xls

 

[Roady]

Yes, this worked until I needed to switch the order so that the column that
is being Conditionally formatted is to the left. Now, when I try to change
the formula to work and hit save/apply, when I go back in there it hasn't
saved any of my adjustments. I think that might be why it's returning a DIV/O
error in blank columns whereas it was not before. Any ideas on how to fix the
formula to work now that the columns are in different order? Thanks, JR

 

[Max]

There's some ambiguity in interpreting the switch scenario you posted
but one of the 2 steps below should deliver what you want
Try it out ..

Either:
Select col A (with A1 active), then apply CF using Formula Is
for the 3 conditions
=AND(COUNT($A1:$B1)=2,$A1<=$B1)
=AND(COUNT($A1:$B1)=2,$A1>$B1,$A1<$B1*1.03)
=AND(COUNT($A1:$B1)=2,$A1>=$B1*1.03)

Or this:
Select col A (with A1 active), then apply CF using Formula Is
for the 3 conditions
=AND(COUNT($A1:$B1)=2,$B1<=$A1)
=AND(COUNT($A1:$B1)=2,$B1>$A1,$B1<$A1*1.03)
=AND(COUNT($A1:$B1)=2,$B1>=$A1*1.03)