Oct 10 2019 09:03 AM
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
Oct 10 2019 09:32 AM
Oct 10 2019 09:33 AM
I've also attached my file
Oct 10 2019 10:40 AM - edited Oct 10 2019 03:14 PM