+ Reply to Thread
Results 1 to 4 of 4

IF statements and ranges

  1. #1
    Registered User
    Join Date
    01-27-2006
    Posts
    12

    Question 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

  2. #2
    Registered User
    Join Date
    01-05-2006
    Posts
    65
    Try

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

  3. #3
    Registered User
    Join Date
    01-27-2006
    Posts
    12
    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.

  4. #4
    Registered User
    Join Date
    01-27-2006
    Posts
    12
    Quote Originally Posted by mphell0
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1