How to nest three AND conditions in an if statement

[Halfdef]

I am trying to put three AND conditions in an IF statement. I am using three conditions as qualifying criteria for being selected into a group.

If they were on the blue team (BN12="Blue Team","Y","N"), ranked the experience (8 CL11>=8) or higher, and said Yes (DI11="Yes") to interest in the team, and will be available to play (AC=YES).

Here is what I have:
=IF(AND(CL11>=8, DI11="Yes", AC="Yes"), BN12="Blue Team","Y","N")

Thank you for the assistance!!

[jeffreybrown]

Can you post a small sample workbook and what the result should be?

[Halfdef]

I cannot share the data, unfortunately. Basically, what I am trying to do is show if the three conditions are met. If so, then I will use text that says "Yes" or something like that, and "No" if not.

I hope that this helps.

[jeffreybrown]

This will do what you ask, but you say three conditions yet I see four.

=IF(AND(CL11>=8,DI11="Yes", AC11="Yes"),"Yes","No")

[Halfdef]

Hi Jeff,

Thank you for the assistance. You are right, I said three, but I meant 4. I also need that BN cell to be "blue team"... I am working on this. But welcome assistance.

Best,
Andrew

[Halfdef]

=IF(AND(CL11>=8,DI11>=8, AC11="Yes", BN12="Blue Team"),"Yes","No")

this is what I have, it seems to work. Does this look right to you?

[jeffreybrown]

Hi Andrew,

Does this work for you?

=IF(AND(CL11>=8,DI11="Yes", AC11="Yes",BN12="Blue Team"),"Yes","No

Yes, your formula looks right and I've tested this above.

[Halfdef]

Hi Jeff,

I will check that last formula you posted out. I have one more question, what if I wanted to put a range in to the formula.

So, rather than CL being equal to or greater than 8, I want another condition where it is between 3-7. How would I put that range in?

Best,
Andrew

[jeffreybrown]

How about...

=IF(AND(CL11>=3,CL11<=7,DI11="Yes", AC11="Yes",BN12="Blue Team"),"Yes","No")

[Halfdef]

I think that worked. I getting called to dinner Need to heed her call. Thank you for the assistance. Might have more questions if you are up for the help!

[jeffreybrown]

If you have some more questions, we're here to help. I'll only be around for another 30 minutes or so, but back tomorrow.

[Halfdef]

Hi All,

I have a new twist on this. One of the points in the answer scale for the survey that was used to get this data is text (eg ”Very<br>likely<br>10”), not numeric. Rather than doing find and replace, I need to account for this is the formulas.

I was thinking that I could insert something like or(DF12>=8,DF12=”Very<br>likely<br>10”) into the formula. But I do not know where exactly I need to insert this to have it work. Please assist.

Thank you!

[Halfdef]

Hi,

There is one more thing that I am struggling with at the moment. I need to fix this formula.

=IF(AND(AC13="Blue Team", COUNTA(BK13:BN13, 0, 1), BY13>=3, BY13<=7,CQ13>=3, CQ13<=7), COUNTA(AQ13, AR13, AS13, AV13, AT13),"Yes","No")

I am trying to have the formula tell me if AC cell is 'Blue team" and cells BK to BN have content in them, and BY and CQ cell values are between 3 and 7, and cells AQ, AR, AS, AV, and AT have contents in them.

If this is unclear please ask and I will elaborate. Thank you for the assistance!