Forum Discussion
Eddy162
Oct 24, 2022Copper Contributor
Help with Excel logical formula budgets vs actuals Rag staus.
Hi all,
I am needing help with a formula. Confused to what would be best or could work, ifs ands or etc.
I am trying to automate RAG staus.
1.
If the variance of a budget vs actuals is less than $5000 return "green" if over than then look at if actuals are within 5%(over and under) of budget returns text "green", if great than 10% variance shows text "red", anything between those values returns text "yellow"
2.
If possible the boss was really hoping for the rags to match below.
Green = <= 5% and <= 5k
Amber = >5% <10% and >5k <10k
Red= >=10% and >= 10k
Appreciate any help.
Let's say Budget is in A2 and Actual in B2.
=IF(OR(ABS(B2-A2)<=5000,ABS(B2-A2)/A2<=5%),"Green",IF(OR(ABS(B2-A2)<=10000,ABS(B2-A2)/A2<=10%),"Amber","Red"))
This can be filled down.