Conditional Format Based On Percentage Variance

[srizki]

I have budgeted numbers, actual and variance, I want variance cell to highlight Red Yellow or Green if the variance percentage is in the specified ranges. The variance cells have values and not percentages. So the conditional formula will calculate percentages and then apply the colors, and of course there is not a fixed value, values will be in Actuals and in budget.
So as soon the actuals are input the variance will show the difference in value and apply color based on percentage of difference

[Admin]

Hi Srizki,

Welcome to ExcelFox !!

Could you please attach a sample workbook ?

[Excel Fox]

So if your Columns A,B and C have the Budget, Actual and Variance, then use the following formulas in conditional format

=(B2-A2)/A2>=10% (for any variance greater or equal to 10%)
=(B2-A2)/A2>=5% (for any variance greater or equal to 5%)
=(B2-A2)/A2<=5% (for any variance less than 5%)

Note that the order of the formatting rules is important. Excel 2007 has other ways to control when to stop with any one rule, but that should be another topic.

Check the attachment for example.

[srizki]

Thank you very much.