If(AND statement will not evaluate

[bboland]

I am a bit of novice, so any help is appreciated. I have a IF(AND statement that tests several criteria. I have a bunch of these statements that work fine, but thislast one I can not get to work. I have a cell called AP1P2 that contains a > depending on what I enter. I am trying to pull the sign into a equation. The values I want to compare is in A1 with value 1, A2 with value of 2, and A3 with value of for example. So here is what I have:

=IF(AND(A1&AP1P2&"=0", A2&AP1P2&"=0",A3&AP3P4&"=0"), 1, 0)

I am trying to get it to build the statement and evaluate it. Say I enter a > in the cell named AP1P2 it would perform the folowing:

=IF(AND(1>=0, 2>=0, 3>=0), 1, 0)

=IF(AND(True, True, True), 1, 0) So it returns a 1.

When I step through the formula it tells me the following:

=IF(AND("1>=0", "2>=0", "3>=0"), 1, 0) then it says
=IF(AND(#value!), 1, 0) and will not evaluate the expression.

Can anyone help a novice out? Thanks, Brian

 

[mikerickson]

Looking at your first formula:
the first term of the AND is a string, not a logical value
Even if the string looks like "A1+3=0", Excel will see only the string and not evaluate it as a formula.

When constructing complex logicals, I work from the inside out.
For example,
first I check that =(A1=B1) returns True or False correctly,
Then I check that =(A1>3) does what I think it should.
Then I check =AND(A1=B2,A1>3)
Finaly I insure that =IF(AND(A1=B2,A1>3),"BIG and Same","other") is right.

Building from the inside outward pinpoints where syntax errors are.

 

[bboland]

Thank you for replying. That makes alot of sense. I broke it apart and looked at what was working. I can make it work fine except when I try to pull the > or< from the named cell AP1P2. It works fine if I type the < or > directly in the formula. Any ideas?

 

[barry houdini]

You can't normally build formulas this way, excel doesn't evaluate them automatically, you're just building text strings.

You can try using the EVAL function from morefunc add-in and use the formula

=EVAL(A1&AP1P2&"=0")*EVAL(A2&AP1P2&"=0")*EVAL(A3&AP1P2&"=0")

 

[bboland]

Thanks so much, I got it to work using your suggestion. B.

 

[mikerickson]

I'm glad it works for you, but I have trouble understanding what AP1P2 means.