Conditional format

[colin38]

I have 2 columns (Actual Cost £ and Budget £)

I am trying to apply the following conditions:

1. Actual cost = budget to 4.99% below budget = Fill Green

2. Actual Cost = 4.99% to 100% below budget = Fill Gold

3. Actual cost = budget to 4.99% above budget = Fill Amber

4. Actual cost = 4.99% above to 100% above budget = Fill Red

Completely at a loss with this and would appreciate some assistance.

Many thanks

[martindwilson]

what are your actual limits?
you have conflicts already e.g
4.99 below budget could apply to either of these
1. Actual cost = budget to 4.99% below budget = Fill Green
2. Actual Cost = 4.99% to 100% below budget = Fill Gold

[colin38]

Apologies.
Should read:
1. Actual cost = budget to 4.99% below budget = Fill Green
2. Actual Cost = 5.00% to 100% below budget = Fill Gold

If the conditions were based on % i could do this with no problems but as i am looking at a cost i am unsure as to how to apply the formats.

I can do this by adding in two new colums with the range and then format using the between function but this is a very large spreadsheet and would take forever as all values are different.

I am hoping that i can do something like (if actual = budget * %) in the format box.

[Ganelon]

Hello Colin,

I think this might be what you're looking for (Assuming Excel 2007)
Provided cost is in Column B and Budget in Column C, enter conditions in formula value in this order:
2: =b2<=c2*0.95
1: =b2<=c2
4: =b2>=c2*1.05
3: =b2<c2*1.05

Then mark the rules to stop if true.

There are other ways to do it, but I believe this is the easiest. If you want it to work with older versions you can only use three conditions though, so need to make one of the colours your base and use an if function to blend in the above.

Hope this helps

[colin38]

Superb, thanks for solving my long standing headache.