Conditional Format Based On Percentage Variance

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

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.

Hi,

Thank you for your help.

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

Thank you

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

Try using ABS()