iF STATEMENTS WITHIN CONDITIONAL FORMATS

[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

 

[Bob Phillips]

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)

 

[Max]

One way ..

Assuming this table below is in C14

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

 

[Guest]

Thanks - a working with both of these i've managed to sort it...

 

[Max]

You're welcome !
Glad to hear that ..