excel formula question

[Rlopez24]

Can you do a If or with a If and nested in it?


So i am thinkig it would be something like this if it is possible:


=IF(OR(And(first condition, second condition, …, etc)),If(and(first condition, second condition, …, etc)),Y,N)

 

[SirJB7]

Hi, Rlopez24!

As far as I can remember 2007 version supported 64 nested IF which seemed to be far more than someone could try to write, and sincerely in 2010 version I didn't checked it... but I guess that this number would be enough for you. If not, I want to see that formula right now.

Regards!

 

[Rlopez24]

i wasn't worried about the amount of nested items but more the the ability to do a if statement that has a Or and a And within it.


the formula that i am tryng that isn't working is as such:


=IF(OR(AND(F2="Stewart",BJ2<>"MFC",BN2="#N/A"),if(AND(F2="Stewart",BJ2="MFC",BN2="#N/A",B2<>"#N/A")),"Y","N")

 

[jason]

could you explain what you're trying to accomplish? perhaps theres and easier/better/faster way.


and just to answer your question: you can nest basicaly any fucntions together as long as the nested function(s) return the correct type of data for main functions arguments.

 

[Rlopez24]

to clarify if needed i what the formula to do this:


if cell F2 = "Stewart" and cell BJ2 doesn't = "MFC" and cell BN2 = "#N/A"


or


if cell F2 = "Stewart" and cell BJ2 = "MFC" and cell BN2 = "#N/A" and BO2 doesnt="#N/A"


true = "Y"

false - "N"

 

[SirJB7]

Hi, Rlopez24!

Sorry I didn't understand at a first glance. Regarding your formula, in which you can nest OR & AND functions as needed, it's doing this:

- when F2="Stewart" and BJ2<>"MFC" and BN2="#N/A"... wait!

BN2 is being tested against a string constant "#N/A" and not against a not available error value which displays as #N/A. If this is the case replace the BN2 test with ISNA(BN2) criteria.

Regards!