+ Reply to Thread
Results 1 to 7 of 7

Combination of IF, OR AND formulas

  1. 12-13-2015, 10:33 AM #1
    WendyvdV
    WendyvdV is offline
    Registered User
    Join Date
    12-17-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    29

    Combination of IF, OR AND formulas

    Need help on following example:

    This is how it looks:

    Cell B3 can have these values (text): Blank, D or L
    Cell B4 can have these values (text): Blank or D
    Cell B5 can have these values (text): Blank or KDV

    I need a formula that will give these outcomes;

    If cell B3 or B4 is blank = "OK"
    If cell B3 or B4 is blank and B5 is KDV = "Spare"
    If cell B3 is D, B4 is D and B5 is KDV = "OK"
    If cell B3 is L, B4 is D and B5 KDV = "A not M"
    If cell B3 is D, B4 is D and B5 is blank = "D"
    If cell B3 is L, B4 is D and B5 is blank = "Middag"

    Its driving me crazy, hopefully someone can help me out....
    Reply With QuoteRegister To Reply
  2. 12-13-2015, 10:41 AM #2
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,813

    Re: Combination of IF, OR AND formulas

    Try

    =IF(OR(ISBLANK(B3),ISBLANK(B4)),"OK",IF(AND(B3="D",B4="D",B5="KDV"),"OK",IF(AND(B3="D",B4="D",B5=""),"D",IF(AND(B3="L",B4="D",B5="KDV"),"A not M",IF(AND(B3="L",B4="D",B5=""),"Middag")))))

    or to be consistent


    =IF(OR(ISBLANK(B3),ISBLANK(B4)),"OK",IF(AND(B3="D",B4="D",B5="KDV"),"OK",IF(AND(B3="D",B4="D",ISBLANK(B5)),"D",IF(AND(B3="L",B4="D",B5="KDV"),"A not M",IF(AND(B3="L",B4="D",ISBLANK(B5)),"Middag")))))
    Last edited by JohnTopley; 12-13-2015 at 10:44 AM.
    Reply With QuoteRegister To Reply
  3. 12-13-2015, 10:43 AM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,304

    Re: Combination of IF, OR AND formulas

    Start with the most (positive) conditions and work back.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I'll leave you to try the rest

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 12-13-2015, 10:45 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,304

    Re: Combination of IF, OR AND formulas

    @John: if you start with
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    it will take precedence over the next condition which should result in "Spare".

    Regards, TMS
    Reply With QuoteRegister To Reply
  5. 12-13-2015, 10:49 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Combination of IF, OR AND formulas

    I'm really slow today....

    =IF(AND(B5<>"kdv",OR(B3="",B4="")),"OK",IF(AND(B5="KDV",OR(B3="",B4="")),"Spare",IF(AND(B3="D",B4="D",B5="KDV"),"OK",IF(AND(B3="L",B4="D",B5="KDV"),"A not M",IF(AND(B3="D",B4="D",B5=""),"D",IF(AND(B3="L",B4="D",B5=""),"Middag",""))))))
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  6. 12-13-2015, 11:21 AM #6
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,813

    Re: Combination of IF, OR AND formulas

    I missed (out the) "spare" condition!

    =IF(AND(OR(B33="",B4=""),B5="KDV"),"Spare",IF(AND(B3="D",B4="D",B5="KDV"),"OK",IF(AND(B3="D",B4="D",B5=""),"D",IF(AND(B3="L",B4="D",B5="KDV"),"A not M",IF(AND(B3="L",B4="D",B5=""),"Middag","OK")))))

    Default" is B3 or B4 blank (and B5)
    Last edited by JohnTopley; 12-13-2015 at 11:52 AM.
    Reply With QuoteRegister To Reply
  7. 12-14-2015, 09:57 AM #7
    WendyvdV
    WendyvdV is offline
    Registered User
    Join Date
    12-17-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    29

    Re: Combination of IF, OR AND formulas

    Great this works! Thank you so much!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. combination of =OFFSET() and =ADDRESS() formulas with =VLOOKUP() formula
    By okcsteve in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-24-2015, 12:34 AM
  2. rolling order forecast - order planning excel combination of formulas
    By confused44 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-17-2015, 02:01 AM
  3. Not Sure what formula will complete the task?? Combination of formulas??
    By Bmacnab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2014, 12:42 PM
  4. Combination Sum / Vlookup / Min / If Formulas
    By Formula Junkie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2014, 09:18 PM
  5. Need help figuring out combination of vlookup, max, index and match formulas
    By cobo1981 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-06-2014, 03:58 PM
  6. Create a multiple combination pivot chart with a tbl that has multiple formulas
    By davisfs2007 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-14-2013, 08:39 PM
  7. Combination of formulas
    By Steptwo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-15-2013, 12:24 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1