Nested AND statements in Excel

kathyb10

New Member
Joined
Nov 8, 2011
Messages
11
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

Perhaps this
Code:
=if(A1,"Black","")&if(A2,"Blue","")&...
and so on.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Nov 8, 2011
Messages
11
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

New Member
Joined
Nov 8, 2011
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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

New Member
Joined
Nov 8, 2011
Messages
11

ADVERTISEMENT

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-known Member
Joined
Mar 26, 2007
Messages
9,115
Well, you could break the formula into chunks, and put one chunk into each row.
For example
Code:
=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
Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top