IF statements 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=515499

IF statements and ranges
 

Try

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


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=515499

IF statements and ranges
 

Hey guys,

Just wanted to let you know that I have it figured out. Someone over
at MrExcel helped me out.

Here's the answer:
=IF(ABS(N8-O8)<=0.078,"OK",N8-O8)


I didn't realize excel had the Absolute # formula in it.


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

IF statements and ranges
 

mphell0 Wrote:
Try

=IF(ABS(N1-O1)=0.078,N1-O1,"OK")
Whoops. Looks like you beat me to the punch. Thanks for your help.
That forumla did the trick.

You took the reverse approach, but both work just fine.


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