Conditional Formatting with Percentages - only 2 of 3 working
 

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!

Conditional Formatting with Percentages - only 2 of 3 working
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roady" wrote:
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!

Conditional Formatting with Percentages - only 2 of 3 working
 

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" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roady" wrote:
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!

Conditional Formatting with Percentages - only 2 of 3 working
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roady" wrote:
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