Nested AND statements in Excel

[kathyb10]

Hi I want to write a formula in Cell A6 that says, if Cell A1= True, write "Black", and if Cell A2 = True, write "Blue", and if Cell A3 = True, write "Yellow". But if all of them are False, then leave the cell blank.

So it would be possible for A6 to say Black, Blue. Or it could say Blue and Yellow.

I don't want the logic that all cells have to be true for B6 to display anything. which is the basic IF(AND statement, or IF(OR.

 

[Gerald Higgins]

Hi, welcome to the board.

Perhaps this

=if(A1,"Black","")&if(A2,"Blue","")&...

and so on.

 

[Aladin Akyurek]

Hi I want to write a formula in Cell A6 that says, if Cell A1= True, write "Black", and if Cell A2 = True, write "Blue", and if Cell A3 = True, write "Yellow". But if all of them are False, then leave the cell blank.

So it would be possible for A6 to say Black, Blue. Or it could say Blue and Yellow.

I don't want the logic that all cells have to be true for B6 to display anything. which is the basic IF(AND statement, or IF(OR.

Something like...

=TRIM(IF(A1," Black","")&IF(A2," Blue","")&IF(A3," Yellow",""))

 

[kathyb10]

Hi Thank you for that. It doesn't seem to be working. I am including the actual code.

=TRIM(IF(Analysis!W22,TRUE,"The Needs Analysis has not passed QA")&IF(Analysis!W34,TRUE,"Audience Analysis has not passed QA")&IF(Analysis!W47,TRUE,"Context Analysis has not passed QA"))

I have some radio buttons on the Analysis worksheet, and if particular ones are selected, the cell says TRUE. So if they show true, I want the summary sheet to give the reason, as shown above. But if they aren't true, I don't want anything in the summary sheet. Can you tell me what's wrong with the formula? I am getting TRUE, TRUE, TRUE appearing on the summary sheet in the cell, now if they are true.

 

[kathyb10]

Actually, it does work if I change the TRUE to fail on the Analysis worksheet. But I think I need an else in there somewhere because now I get the word FALSE appearing if the text doesn't get written. But when I try to put an else in I get too many arguments. Any ideas? Thank you very much for this help.

 

[mikerickson]

That isn't what Alan posted. Try

=TRIM(IF(Analysis!W22," The Needs Analysis has not passed QA","")&IF(Analysis!W34," Audience Analysis has not passed QA","")&IF(Analysis!W47," Context Analysis has not passed QA"),"")

 

[kathyb10]

Yes! Thank you that works great. Now I need to know if I can put each answer on a new line? Is that even possible?

 

[Gerald Higgins]

Well, you could break the formula into chunks, and put one chunk into each row.
For example

=IF(Analysis!W22," The Needs Analysis has not passed QA","")

in one cell, and the next part below, and so on.
The problem with this is that you may get blank rows if, for example, W22 is FALSE but W34 is TRUE.

If that's a problem for you, there are ways round it.

Another possible solution, is to leave the entire formula in a single cell, but create the appearance of the results spreading over more than one line, by inserting a carriage return into the TRUE arguments. From memory, I think CHAR(10) is the carriage return code. So, you could do something like this

=IF(Analysis!W22," The Needs Analysis has not passed QA"&char(10),"")&
IF(Analysis!W34," Audience Analysis has not passed QA"&char(10),"")&...

and so on.
Remember to format the cells to wrap text.
I can't remember if TRIM deletes char(10), it may well do, so play around with it.

 

[kathyb10]

Thank you. It all works perfectly now. Brilliant!
Thanks again.