+ Reply to Thread
Results 1 to 20 of 20

IF AND OR complex statement for accepting limits in a tolerance range

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    IF AND OR complex statement for accepting limits in a tolerance range

    I keep getting the "Too Many Arguments" notice for this equation and I'm wondering if anyone out there can come up with A) either the proper formula; or B) a better way to do it.

    I have this form that is used in production. The guys filling it out put in the criteria and the form is supposed to tell them if it's acceptable or not acceptable.

    I've attached a sample with any sensitive material removed.
    Essentially, what should happen is:
    If cells H, I, J, K, or L are greater than cell E but less than cell D, cell O should mark with an "X" for acceptable.
    If any of those cells do not fall within the limits of cells E and D, cell P should mark with an "X" for not acceptable.

    The problem with my formula is that it's requiring cell H:L to fall within the limits for the "X" to mark acceptable when in reality if only cell H is filled in (as long as it's within limit), the form should be marked acceptable.

    I've thought about switching the formula so that instead of having two separate formulas for cells O(acceptable) and P(not acceptable), we have one field where the value if true would mark Acceptable and the value if false would mark Not Acceptable...but i'm still having trouble working that logic...

    Here are my formulas as they stand:
    For cell O (Acceptable):
    =IF(D4>0,"",IF(E4="","",IF(AND(D4>=0,E4>=0,H4<=D4,H4>=E4,I4<=D4,I4>=E4,J4<=D4,J4>=E4,K4<=D4,K4>=E4,L4<=D4,L4>=E4),"X","")))

    For cell P (Not Acceptable):
    =IF(OR(AND(H4>0,OR(H4<E4,H4>D4)),AND(I4>0,OR(I4<E4,I4>D4)),AND(J4>0,OR(J4<E4,J4>D4)),AND(K4>0,OR(K4<E4,K4>D4))),"X","")
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    What are some example serial numbers? Do they contain text, hyphens, decimals?

  3. #3
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    They can contain decimals to the 1,000th (.125), but may just be whole numbers.

    The reason you see so many zeros in the formulas is becuase excel was marking it acceptable if left blank, which is not the case.
    Last edited by maggiedort; 08-22-2013 at 10:31 AM.

  4. #4
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    How about looking if the MAX of the serial numbers is below the set maximum and the MIN is above the set minimum? Then you only have 2 checks instead of 10, and then you only have to work out something simple for the possible empty cells
    When I say semicolon, u say comma!

  5. #5
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    well that's kind of how i have it right now (H4<=D4,H4>=E4; D4 being the max and E4 being the min) except that the max and min cannot be blank for the form to be acceptable/unacceptable... which is what was happening hence the IF(D4>0,"",IF(E4="","",IF(AND(D4>=0,E4>=0 at the start of the formula

  6. #6
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    I meant using the MIN and MAX functions to replace this piece:

    H4<=D4,H4>=E4,I4<=D4,I4>=E4,J4<=D4,J4>=E4,K4<=D4,K4>=E4,L4<=D4,L4>=E4

    That would reduce the number of arguments, to begin with. Then there is still the problem of the nills, I know, still working on that

  7. #7
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    go it -- thanks -- i'll do that to shorten the equation, still need to find a solution for the real problem though haha

  8. #8
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    How about this for O4?

    =IF(AND(D4>0,E4>0,MAX(H4:L4)<D4,MIN(H4:L4)>E4),"X","")

    MINs and MAXs don't count empty cells

  9. #9
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    And this one in P4

    =IF(AND(D4>0,E4>0,OR(MAX(H4:L4)>D4,MIN(H4:L4)<E4)),"X","")

  10. #10
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    And this one in P4

    =IF(AND(D4>0,E4>0,OR(MAX(H4:L4)>D4,MIN(H4:L4)<E4)),"X","")

  11. #11
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    And you can even combine them to use in both O4 and P4, just put the one for P4 in the last argument of the one for O4:

    =IF(AND(D4>0,E4>0,MAX(H4:L4)<D4,MIN(H4:L4)>E4),"X",IF(AND(D4>0,E4>0,OR(MAX(H4:L4)>D4,MIN(H4:L4)<E4)),"X",""))

  12. #12
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    Holy moly L-Drr, that was lightyears shorter than what I was working on.

    Just make sure to add MAX(H4:L4)>=D4,MIN(H4:L4)<=E4

    to pick up the disqualifying ties in P4
    Last edited by daffodil11; 08-22-2013 at 11:13 AM.

  13. #13
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    Hahah lol

    Good point about the border values

  14. #14
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    =IF(AND(D4>0,E4>0,MAX(H4:L4)<D4,MIN(H4:L4)>E4),"X",IF(AND(D4>0,E4>0,OR(MAX(H4:L4)>D4,MIN(H4:L4)<E4)),"Not Acceptable","Acceptable"))


    This will work I think. The only problem may still be that if H4:L4 are left blank it marks it "Not Acceptable" -- any ideas?

  15. #15
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    I usually work on a puzzle for 15 minutes, then hit refresh to see if I need to punch my monitor.

    Going through a lot of monitors lately..

  16. #16
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    haha -- i've debated throwing my computer out of my window at least 7 times while working on this one

  17. #17
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    Quote Originally Posted by maggiedort View Post
    =IF(AND(D4>0,E4>0,MAX(H4:L4)<D4,MIN(H4:L4)>E4),"X",IF(AND(D4>0,E4>0,OR(MAX(H4:L4)>D4,MIN(H4:L4)<E4)),"Not Acceptable","Acceptable"))


    This will work I think. The only problem may still be that if H4:L4 are left blank it marks it "Not Acceptable" -- any ideas?
    I don't get what you did at the end of the formula...

    To check if H4:L4 are not blank, you could build in IF(SUM(H4:L4)>0

  18. #18
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    I changed the form so that there's only one cell for an answer -- Cell O. so instead of an x being placed in cell O if acceptable or an x being placed in cell P if not acceptable, the true/false will be Acceptable / Not Acceptable.

    I think I'm good with this but I'll try the SUM function Thanks for all the help!!!

    If there's nothing entered into the serial numbers then technically the form IS not acceptable so ... crisis averted thanks again!
    Last edited by maggiedort; 08-22-2013 at 11:28 AM.

  19. #19
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    You're welcome!

    Please mark the thread as solved (above the first post) and consider adding reputation (bottom left corner of a post)

  20. #20
    Registered User
    Join Date
    08-22-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: IF AND OR complex statement for accepting limits in a tolerance range

    Attached is my final example
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2012, 12:00 PM
  2. return number from range if in tolerance
    By BHudPE in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-07-2011, 04:21 PM
  3. Compare numbers to a tolerance range
    By C2AUTOINC in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-30-2009, 06:16 PM
  4. worbook.name not accepting range value
    By suzetter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2005, 06:05 AM
  5. If Statement Limits
    By Lambright44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2005, 09:25 PM

Tags for this Thread

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