Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-905806%22%20slang%3D%22en-US%22%3EFormula%20Required%3A%20Outside%20of%20a%20range%20(plus%20or%20minus)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905806%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20had%20this%20formula%3CBR%20%2F%3E%3DIF(S3%3DR3%2C%22Good%22%2CIF(ABS(S3-R3)%26lt%3B%3D20%25%2C%22Under%22%2C%22Over%22))%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20I%20want%20to%20check%20the%20values%20are%20within%20an%20allowable%20range.%20So%20it's%20whether%20I%20can%20combine%20something%20like%20this%20to%20a%20single%20formula%3F%3CBR%20%2F%3E%3DIF(B1%3DA1%2C%22Good%22%2CIF(ABS(B1-A1)%26lt%3B%3D20%25%2C%22Under%22))%3CBR%20%2F%3E%3DIF(B1%3DA1%2C%22Good%22%2CIF(ABS(B1-A1)%26gt%3B%3D20%25%2C%22Over%22))%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20testing%20two%20percentages%20(Actual%20Expenditure%20vs%20Budgeted%20Expenditure)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20the%20Value%20of%20Cell%20B%20is%20Equal%20to%20Cell%20A%20then%20Good%2C%20we%20are%20within%20the%20acceptable%20parameters.%20However%2C%20if%20Cell%20B%20is%20outside%20of%20a%20range%20of%2020%25%20of%20the%20value%20of%20Cell%20B%20compared%20with%20Cell%20A%20then%20flag%20as%20Under%20if%20it's%20less%20than%2020%25%20(we%20need%20to%20spend%20more)%20or%20Flag%20Red%20if%20it's%20greater%20than%2020%25%20(we%20have%20overspent%20so%20cut%20back).%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20%25s%20change%20over%20time%20with%20the%20amount%20of%20budget%20increases%20cumulatively%20weekly%20but%20the%20actual%20time%20is%20updated%20via%20timesheets%20and%20is%20variable.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CBR%20%2F%3EA%20%26nbsp%3B(budget)%20%26nbsp%3B%20%7C%20%26nbsp%3B%20%26nbsp%3BB%20(time%20elapsed)%20%26nbsp%3B%20%7C%20C%3CBR%20%2F%3E21.37%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%2062.11%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BUnder%20Budget%3CBR%20%2F%3E131.44%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%2026.58%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BOver%20Budget%3CBR%20%2F%3E1.96%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%2014.81%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BGOOD%3CBR%20%2F%3E8.93%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%2080.56%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BUnder%20Budget%3CBR%20%2F%3E87.65%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%206.13%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BOver%20Budget%3CBR%20%2F%3E50%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%2060%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BGOOD%3CBR%20%2F%3E60%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%2050%25%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BGOOD%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20that%20makes%20sense.%20Thanks%20in%20advance%20for%20your%20support.%20Adrian%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-905806%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-905980%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%3A%20Outside%20of%20a%20range%20(plus%20or%20minus)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905980%22%20slang%3D%22en-US%22%3EThis%20should%20work%20%3DIF(AND(A1-B1%26lt%3B0.2%3BA1-B1%26gt%3B-0.2)%3B%22GOOD%22%3BIF(A1-B1%26gt%3B%3D0.2%3B%22OVER%22%3B%22UNDER%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-905989%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%3A%20Outside%20of%20a%20range%20(plus%20or%20minus)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20attached%20my%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-906403%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%3A%20Outside%20of%20a%20range%20(plus%20or%20minus)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-906403%22%20slang%3D%22en-US%22%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20C2%20is%3A%26amp%3Bnbsp%3B%3CBR%20%2F%3E%3DIF(ABS(A2-B2)%26amp%3Blt%3B%3D0.2%2C%22Good%22%2C%3CBR%20%2F%3EIF(A2-B2%26amp%3Bgt%3B0.2%2C%22Over%20Budget%22%2C%3CBR%20%2F%3E%22Under%20Budget%22))%3CBR%20%2F%3EGiven%20that%20expenditures%20are%20cash%20outflows%2C%20let%20me%20accentuate%20that%20an%20%22Under%20Budget%22%20is%20a%20favorable%20variance%2C%20such%20that%20you%20spent%20less%20than%20you%20should%20and%20you%20must%20strive%20to%20spend%20lesser.%20Conversely%2C%20an%20%22Over%20Budget%22%20is%20an%20unfavorable%20variance%2C%20such%20that%20you%20spent%20more%20than%20you%20should%20and%20you%20must%20strive%20to%20spend%20less%20next%20time.%3C%2FLINGO-BODY%3E
adydent67
New 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"))
&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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies