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
In the attached file, the formula in C2 is: =IF(ABS(A2-B2)<=0.2,"Good", IF(A2-B2>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.