IF/AND Formula Problem

[CT]

In column A I need to return a value based on critera found in columns B and C.
If B1="-" and C1="-" return "No Match" in A1.
If B1>0 and C1="Yes" return "SPINS TOL" in A1.
If B1="-" and C1="Yes" return "TOL" in A1
If B1>0 and C1="-" return "SPINS" in A1.

Here is the formula I am using now.
=IF(AND(B1="-",C1="-"),"No Match",IF(AND(B1>0,C1="Yes"),"SPINS
TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B1>0,C1="-"),"SPINS"))))

Here are my results:
A B C
1 No Match - -
2 SPINS TOL 1 Yes
3 SPINS TOL - Yes
4 SPINS 2 -

I cannot get it to return "TOL" in A3. What am I doing wrong?

 

[T. Valko]

The problem is that:

If B1>0 and C1="Yes" return "SPINS TOL" in A1.
If B1="-" and C1="Yes" return "TOL" in A1

When B = "-", that evaluates to be >0 and in your formula this test gets
satisfied first so it never gets to If B1="-" and C1="Yes" return "TOL" in
A1.

So, try this:

=IF(AND(B1="-",C1="-"),"No Match",IF(AND(ISNUMBER(B1),B1>0,C1="Yes"),"SPINS
TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(ISNUMBER(B1),B1>0,C1="-"),"SPINS"))))

 

[Max]

The problem is because any kind of text in col B (which contains mixed data -
text/numbers) will always evaluate as TRUE for numeric checks applied such
as: IF(B1>0

This revision using an additional: ISNUMBER(B1) check will help to
distinguish it where you apply numeric checks on col B. Should do it ..

In A1, copied down:
=IF(AND(B1="-",C1="-"),"No Match",IF(AND(ISNUMBER(B1),B1>0,C1="Yes"),"SPINS
TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(ISNUMBER(B1),B1>0,C1="-"),"SPINS"))))

 

[CT]

Thank you Max and Valko, learn something new every day.

 

[David Biddulph]

You have satisfied the 2nd condition
If B1>0 and C1="Yes" return "SPINS TOL" in A1, so that's what you've got,
and tyou don't go on to do the 3rd test.

If you want to have the 3rd condition
If B1="-" and C1="Yes" return "TOL" in A1
take priority over the 2nd condition
If B1>0 and C1="Yes" return "SPINS TOL" in A1
then reverse the order of the tests.

=IF(AND(B1="-",C1="-"),"No
Match",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B1>0,C1="Yes"),"SPINS
TOL",IF(AND(B1>0,C1="-"),"SPINS"))))

 

[Rick Rothstein \(MVP - VB\)]

The other two responses (so far) have discussed why your code didn't work as
you expected. As for a solution... IF what you posted are the only possible
entries in the B1 and C1, then this formula is more compact than the
approach you were attempting...

=CHOOSE(1+(B1="-")+2*(C1="Yes"),"SPINS","No Match","SPINS TOL","TOL")

If you have other conditions that you did not post, the above can probably
be modified to handle them (depending on how many there are).

Rick

 

[CT]

Thank you Rick, I did simplify my example and I will have other conditions
going forward.

 

[Rick Rothstein \(MVP - VB\)]

Can you tell us what those conditions are? Will there be more than 7 of them
(if so, you will have problems with the nested IF testing)?

Rick

 

[CT]

I want to thank you all (T.Valko, Max, Rick and David) for your quick
responses and explanations to my problem. Rick, I should be topping out at 6
conditions so hopefully will not have to worry about the 7th. David, thank
you for expanding the logic.

 

[Max]

Welcome, CT