IF Formulas with AND and OR in them

Thanks:  0
Likes:  0

# Thread: IF Formulas with AND and OR in them

1. ## 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. ## 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. ## 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.

4. ## 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. ## 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. ## 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. ## Re: IF Formulas with AND and OR in them

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

8. ## 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

#### Posting Permissions

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