if formula with between value as a criteria
 

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

if formula with between value as a criteria
 

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

"AHizon via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

if formula with between value as a criteria
 

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))


"AHizon via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

if formula with between value as a criteria
 

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" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

if formula with between value as a criteria
 

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

FSt1 wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

if formula with between value as a criteria
 

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,









"AHizon via OfficeKB.com" wrote:

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

FSt1 wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

if formula with between value as a criteria
 

This formula works too. Thanks all for your help!

xlm wrote:
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]
between 1 and -1? Please let me know if there is a way to modify this
formula.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1