iF STATEMENTS WITHIN CONDITIONAL FORMATS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a conditional format into variance analyis based on %,
not true variance.

For instance
actuals = 50
Budget = 40
actuals greater budget show Green

actuals = 38.4
Budget = 40
actuals less than budget, but within 5% show amber

actuals = 20
Budget = 40
actuals less than budget, and greater than 5% show red

The variance on the sheet is in whole numbers not percentage - any ideas?

Many thanks
 
You create 3 conditions, one based upon each condition.

=actuals>budget

=actuals/budget>=95%

=actuals/budget<95%

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
One way ..

Assuming this table below is in C1:D4

Actuals Budget
50 40
38.4 40
20 40

Select C2:C4

Click Format > Conditional Formatting

Make the settings:

Cond1: Formula is: =C2>D2
Format > Patterns > Green

Cond2: Formula is: =AND(C2<=D2,(D2-C2)/D2<=0.05)
Format > Patterns > Amber

Cond3: Formula is: =(D2-C2)/D2>0.05
Format > Patterns > Red
Click OK

C2:C4 will be filled with colors: Green, Amber, Red
 
Back
Top