Formula Required: Outside of a range (plus or minus)

Copper Contributor

I had this formula
=IF(S3=R3,"Good",IF(ABS(S3-R3)<=20%,"Under","Over"))

But I want to check the values are within an allowable range. So it's whether I can combine something like this to a single formula?
=IF(B1=A1,"Good",IF(ABS(B1-A1)<=20%,"Under"))
=IF(B1=A1,"Good",IF(ABS(B1-A1)>=20%,"Over"))

I'm testing two percentages (Actual Expenditure vs Budgeted Expenditure)

If the Value of Cell B is Equal to Cell A then Good, we are within the acceptable parameters. However, if Cell B is outside of a range of 20% of the value of Cell B compared with Cell A then flag as Under if it's less than 20% (we need to spend more) or Flag Red if it's greater than 20% (we have overspent so cut back). 

 

The %s change over time with the amount of budget increases cumulatively weekly but the actual time is updated via timesheets and is variable. 


A  (budget)   |    B (time elapsed)   | C
21.37%      | 62.11%                      Under Budget
131.44%      | 26.58%                     Over Budget
1.96%      | 14.81%                        GOOD
8.93%      | 80.56%                      Under Budget
87.65%      | 6.13%                      Over Budget
50%              | 60%                      GOOD
60%              | 50%                      GOOD

 

Hope that makes sense. Thanks in advance for your support. Adrian

3 Replies
This should work =IF(AND(A1-B1<0.2;A1-B1>-0.2);"GOOD";IF(A1-B1>=0.2;"OVER";"UNDER"))

@PascalKTeam 

I've also attached my file

&nbsp;
In the attached file, the formula in C2 is:&nbsp;
=IF(ABS(A2-B2)&lt;=0.2,"Good",
IF(A2-B2&gt;0.2,"Over Budget",
"Under Budget"))
Given that expenditures are cash outflows, let me accentuate that an "Under Budget" is a favorable variance, such that you spent less than you should and you must strive to spend lesser. Conversely, an "Over Budget" is an unfavorable variance, such that you spent more than you should and you must strive to spend less next time.