IF Formulas with AND and OR in them
IF Formulas with AND and OR in them
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: IF Formulas with AND and OR in them

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    127
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default IF Formulas with AND and OR in them

     
    Hi, does anybody know if it is possible to have a nested =IF formula, with AND and OR options? Or does AND always only provide a TRUE and FALSE return?

    I am basically trying to say;
    'If cell A,B,C,D contains something specific, and cell E contains either X or Y, then do this...'

    It's a meaty formula that I have created, so don't ask me to paste it as it will confuse things!

  2. #2
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

    @NJS1982, the short answer is ... yes, you can use AND and OR within nested IFs.

    However, rest assured that your formula won't go over people's heads here. It's always easier and more efficient to answer a question or provide a solution when we can see the formula or data set.

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    127
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

    OK, now there is a challenge! OK, this is my formula, which works...

    =IF(AY5="Y",CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AJ5," project, with a focus on beneficiaries from a BAME background."),
    IF(AZ5="Y",CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AJ5," project, with a focus on beneficiaries with a disability."),
    IF(BA5="Y",CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ","AH4","project, with a focus on younger beneficiaries."),
    IF(BB5="Y",CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AJ5," project, with a focus on older beneficiaries."),
    IF(AND(AY5="",AZ5="",BA5="",BB5=""),"",CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,,"'",". This project lists its main activity as ",AJ5,"."))))))

    My challenge is to add in another check to see if the contents of cell AW5 are "Capital" OR "Mixed", or "Revenue" and not provide that additional text and retain the formula above. The formula I tried became really big, but it didn't work, I assumed because of the way I had mixed AND with OR (I did a classic Google search and came up scratching my head):

    =IF(AND(AY5="Y",AT5="Revenue")),CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AJ5," project, with a focus on beneficiaries from a BAME background."),
    =IF(AND(AY5="Y"(OR(AT5="Capital","AT5="Mixed")))),CONCATENATE("Funding under our organisations ",AL5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AJ5," project, with a focus on beneficiaries from a BAME background."," This project ists its activity as ",AV5," - ",AW5),
    ...then repeated in a similar fashion with the other four 'IF' formulas...

    AT5 contains one of three choices of text; Capital, Revenue or Mixed.
    AY5 either has a 'Y' or is blank
    The other cells are all text cells, with specific categorisations, in this order:
    AL4 = programme name,
    D5 = project title,
    AJ5 = sport type,
    AV5 = building type and
    AW5 = sub-facility type,
    ...the latter two being the missing fields in my original working formula, which are only relevant to Capital and Mixed, not Revenue.
    Last edited by NJS1982; Dec 7th, 2017 at 02:59 PM.

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    127
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

    I think I may have cracked it myself...I had another post and somebody helpfully advised me about '(blank)' not being blank at all..another story entirely. But, my formula is:

    =IF(AV5="(blank)",IF(AX5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AI5," project, with a focus on beneficiaries from a BAME background."),IF(AY5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AI5," project, with a focus on beneficiaries with a disability."),IF(AZ5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ","AH4","project, with a focus on younger beneficiaries."),IF(BA5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AI5," project, with a focus on older beneficiaries."),IF(AND(AX5="",AY5="",AZ5="",BA5=""),"",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,,"'",". This project lists its main activity as ",AI5,".")))))),CONCATENATE(IF(AX5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AI5," project, with a focus on beneficiaries from a BAME background."),IF(AY5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AI5," project, with a focus on beneficiaries with a disability."),IF(AZ5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ","AH4","project, with a focus on younger beneficiaries."),IF(BA5="Y",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,"'",". This project is a ",AI5," project, with a focus on older beneficiaries."),IF(AND(AX5="",AY5="",AZ5="",BA5=""),"",CONCATENATE("Funding under our organisations ",AK5," funding programme for a ",AT5," project titled ","'",D5,,"'",". This project lists its main activity as ",AI5,"."))))))," This funding has contributed towards a ",AV5," - ",AW5))

  5. #5
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

    @NJS1982, this formula is 10x longer than it needs to be.

    I was working out a solution to your Post #3 , but now you've posted Post #4 , which has completely different column information; and I don't know what you mean by "'(blank)' [isn't] blank at all."

    I'm happy to shorten this for you, but I'd need to know why the column information has changed and what you mean by the 'blank' comment.

  6. #6
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

    @NJS1982, see how this works in lieu of your most recently posted formula version:

    ="Funding under our organisation"&CHAR(39)&"s "&AK5&" funding programme for a "&AT5&" project titled "&CHAR(39)&D5&"."&CHAR(39)&" This project "&IF(ISNUMBER(MATCH("Y",AX5:BA5,0)),"is a "&CHOOSE(MATCH("Y",AX5:BA5,0),AI5,AI5,AH4,AI5)&" project, with a focus on "&CHOOSE(MATCH("Y",AX5:BA5,0),"","","younger ","older ")&"beneficiaries"&CHOOSE(MATCH("Y",AX5:BA5,0)," from a BAME background."," with a disability",".",".")," lists its main activity as "&AI5&"."&IF(OR(AV5="(blank)",AV5=""),""," This funding has contributed towards a "&AV5&" - "&AW5&"."))

  7. #7
    Board Regular
    Join Date
    Sep 2009
    Posts
    127
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

    Wow-eee @ErikTyler, that is genius! Many, many thanks!!

  8. #8
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formulas with AND and OR in them

      
    @NJS1982, as a general rule, when you find yourself repeating information in a cell, it can be condensed, leaving only the changing parts to ... well, change. And it's easier to add, remove or change optional items later if need be, when the formula stays as short as possible.

    Glad the shortened version worked in context (never a given when there are that many moving parts).

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com