Conditional Format Based On Percentage Variance

[Garfoid]

Hi

I have a sheet that has "Actual" in B7, "Forecast" in C7 & "Budget" in D7

I want the "Actual" cell to:-

turn green if it is within 5% of the Budget
turn amber if between 5% and 10% of the Budget
turn red if over 10% of the Budget

I cannot get the formulaue right in the conditional formatting

I also have the above repeated in rows 12,17,22,27,32 &37. Do I have to write the formulaes again for each cell?

Thanks in advance

[Excel Fox]

Hi Garfoid,

Welcome to ExcelFox

You could use the following three criteria for you condition in B7 and just copy the format to the other rows

RED =B7/D7-1>10%
AMBER =AND(B7/D7-1>=5%,B7/D7-1<=10%)
GREEN =B7/D7-1<5%

Check attachment for reference.

[Garfoid]

Hi,

Thank you for your help.

This works on numbers above the budgeted number but not below, can you help with this?

Thank you

[Excel Fox]

You just need to fix the formulae I gave, to cover for below thresholds also.

Try using ABS()