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
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies