IF statments and ranges
 

Hey everyone,

I'm trying to compare two columns, and use a threshold for what type of
differences are considered OK. I developed a forumla, but just realized
that it will not work for all instances. Here's my dilema.

........... Col A ............... Col B ............ Col C
Row 1 .. 5,000 ............... 3,000 ............ OK
Row 2 .. 3,000 ............... 5,000 ............ -2000

My current formula, to account for a 0.078 threshold difference is:
=IF(N8=O8,"OK",
IF(N8=(O8+0.078),"OK",
IF(N8=(O8-0.078),"OK",
IF(O8<=(N8+0.078),"OK",
IF(O8<=(N8-0.078),"OK",N8-O8)))))

I realized that this will only work for Data in Row 2. So in Row 2,
the values will fail all four IF statements, and the result will be
-2000 in Column C - this is correct. However, in Row 1, even though
the difference is larger than 0.078, the values pass for the 1st and
2nd IF statements, so they receive a value of "OK".

Is there a way to force the formula to have to pass all statements,
before assigning "OK"?

Is there a way where I can say, IF ((Difference between N1 and O1) =
0.078 or more, THEN (N1 - O1), "OK"))


Thanks for your help


--
Idz21
------------------------------------------------------------------------
Idz21's Profile: http://www.excelforum.com/member.php...o&userid=30910
View this thread: http://www.excelforum.com/showthread...hreadid=515497

IF statments and ranges
 

=IF(N1-O1=0.078,N1-O1,"OK")

Vaya con Dios,
Chuck, CABGx3



"Idz21" wrote:


Hey everyone,

I'm trying to compare two columns, and use a threshold for what type of
differences are considered OK. I developed a forumla, but just realized
that it will not work for all instances. Here's my dilema.

........... Col A ............... Col B ............ Col C
Row 1 .. 5,000 ............... 3,000 ............ OK
Row 2 .. 3,000 ............... 5,000 ............ -2000

My current formula, to account for a 0.078 threshold difference is:
=IF(N8=O8,"OK",
IF(N8=(O8+0.078),"OK",
IF(N8=(O8-0.078),"OK",
IF(O8<=(N8+0.078),"OK",
IF(O8<=(N8-0.078),"OK",N8-O8)))))

I realized that this will only work for Data in Row 2. So in Row 2,
the values will fail all four IF statements, and the result will be
-2000 in Column C - this is correct. However, in Row 1, even though
the difference is larger than 0.078, the values pass for the 1st and
2nd IF statements, so they receive a value of "OK".

Is there a way to force the formula to have to pass all statements,
before assigning "OK"?

Is there a way where I can say, IF ((Difference between N1 and O1) =
0.078 or more, THEN (N1 - O1), "OK"))


Thanks for your help


--
Idz21
------------------------------------------------------------------------
Idz21's Profile: http://www.excelforum.com/member.php...o&userid=30910
View this thread: http://www.excelforum.com/showthread...hreadid=515497