iF STATEMENTS WITHIN CONDITIONAL FORMATS
 

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.

=actualsbudget

=actuals/budget=95%

=actuals/budget<95%

--

HTH

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


"E Halliday" <E wrote in message
...
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

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: =C2D2
Format Patterns Green

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

Cond3: Formula is: =(D2-C2)/D20.05
Format Patterns Red
Click OK

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"E Halliday" <E wrote in message
...
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

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


"Max" wrote:

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: =C2D2
Format Patterns Green

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

Cond3: Formula is: =(D2-C2)/D20.05
Format Patterns Red
Click OK

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"E Halliday" <E wrote in message
...
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're welcome !
Glad to hear that ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"E Halliday" wrote in message
...
Thanks - a working with both of these i've managed to sort it...