IF Formulas with AND and OR in them

[NJS1982]

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!

 

[inactiveuserps07]

@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.

 

[NJS1982]

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.

 

[NJS1982]

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))

 

[inactiveuserps07]

@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.

 

[inactiveuserps07]

@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&"."))

 

[NJS1982]

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

 

[inactiveuserps07]

@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).