IF AND functions

[Mary-Anne]

Hi, I'm trying to create an answer using a combination of "and" and "if"
statements for a number of cells - without success. I've done the following
formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2). Can anyone help
me with what I'm missing to make the formula look for the "true" or "false"
result across the 4 cells? Thanks

 

[T. Valko]

=IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2)

You don't want to quote numbers: "0"

Try it like this:

=IF(AND(AA2="p",Q2=0,U2=0,T2="nil"),0,Q2)

 

[Mary-Anne]

yes the problem is that even if T2 and U2 are not equal to 0 - it is giving
me the value of Q2 as the answer

 

[Mary-Anne]

Hi Biff, I think maybe my assumptions of true vs false may be the problem.
Column Q is either value or 0, column T is either nil or something else
(alpha numeric combinations) row U is 0 - 4. If there is value - more than 0
in Q and either nil or 0 in T or U columns I want it to to give me the value
of Q. Currently with the below formulas it gives me the value of Q whether U
or T meet my true criteria or not.

 

[T. Valko]

I'm confused!

Is nil slang for an empty cell or do you mean the literal word nil?

 

[Mary-Anne]

'nil' is the literal word

I'm confused!

Is nil slang for an empty cell or do you mean the literal word nil?

 

[T. Valko]

If there is value - more than 0 in Q and

either nil or 0 in T or U columns
I want it to to give me the value of Q.

Ok, that sounds like an OR condition for the second line:

AND Q>0, OR T="nil", U=0

Try this:

=IF(AND(AA2="p",N(Q2)>0,OR(U2=0,T2="nil")),Q2,0)

If U2 is an empty cell it will evaluate as 0. Is that something you'd need
to account for?