if formula with between value as a criteria

[AHizon via OfficeKB.com]

What formula do I use if I'd like to use the if formula but set the criteria
between 2 values? Below is my example.
=IF(R29=T29, "OK", IF(R29-T29<1, "OK", "Off by $"&ROUND(R29-T29,0)))

If I add a 3rd criteria to display "OK" when it's greater than -1, the
formula doesn't calculate right. I'd like the second if statement to be
between 1 and -1. Is there any way to modify this existing formula so that
it only says "OK" when it's equal to each other or when the difference is
between 1 and -1? Please let me know if there is a way to modify this
formula.

 

[Sheeloo]

=IF(ABS(R29-T29)<1,"OK","")

 

[bpeltzer]

Instead of a separate test, you can just compare abs(r29-t29) to the
tolerance limit:
=IF(R29=T29, "OK", IF(abs(R29-T29)<1, "OK", "Off by $"&ROUND(R29-T29,0)))
And since you're showing the same result (OK) for an exact match as a near
match, you don't even need the separate test for equality:
=if(abs(r29-t29)<1,"OK","Off by $" & round(r29-t29,0))

 

[FSt1]

hi
try this..
=IF(R29=T29,"OK",IF(AND(R29-T29>-1,R29-T29<1),"OK","Off by
$"&ROUND(R29-T29,0)))

regards
FSt1

 

[AHizon via OfficeKB.com]

Great, that formula did it...Thanks so much!

hi
try this..
=IF(R29=T29,"OK",IF(AND(R29-T29>-1,R29-T29<1),"OK","Off by
$"&ROUND(R29-T29,0)))

regards
FSt1

What formula do I use if I'd like to use the if formula but set the criteria
between 2 values? Below is my example.

[quoted text clipped - 6 lines]

between 1 and -1? Please let me know if there is a way to modify this
formula.

 

[xlm]

Hi AHizon

if you need the formula to only says "OK" when it's equal to each other or
when the difference is between 1 and -1, then that formula doesn't take into
account of your
requirement. if cell A2 = 1 and cell B2 =2 which give you -1, the result is
False
Unless I have made a wrong assumption, then I apologize

try this shorter formula, you need to change the cell's references to yours

=IF(OR((A2=B2),AND(A2-B2<=1,A2-B2>=-1)),"ok","off by
$"&ROUND(A2-B2,0))

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

Great, that formula did it...Thanks so much!

hi
try this..
=IF(R29=T29,"OK",IF(AND(R29-T29>-1,R29-T29<1),"OK","Off by
$"&ROUND(R29-T29,0)))

regards
FSt1

What formula do I use if I'd like to use the if formula but set the criteria
between 2 values? Below is my example.

[quoted text clipped - 6 lines]

between 1 and -1? Please let me know if there is a way to modify this
formula.

 

[AHizon via OfficeKB.com]

This formula works too. Thanks all for your help!

Hi AHizon

if you need the formula to only says "OK" when it's equal to each other or
when the difference is between 1 and -1, then that formula doesn't take into
account of your
requirement. if cell A2 = 1 and cell B2 =2 which give you -1, the result is
False
Unless I have made a wrong assumption, then I apologize

try this shorter formula, you need to change the cell's references to yours

=IF(OR((A2=B2),AND(A2-B2<=1,A2-B2>=-1)),"ok","off by
$"&ROUND(A2-B2,0))

HTH

Great, that formula did it...Thanks so much!

[quoted text clipped - 11 lines]