Forum Discussion

Eddy162's avatar
Eddy162
Copper Contributor
Oct 24, 2022

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. 

 

  • Eddy162 

    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.

     

Resources