| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF/AND Formula Problem
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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
The problem is that:
If B10 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),B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(ISNUMBER(B1),B10,C1= "-"),"SPINS")))) -- Biff Microsoft Excel MVP "CT" wrote in message ... 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
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(B10 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),B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(ISNUMBER(B1),B10,C1= "-"),"SPINS")))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "CT" wrote: 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
Thank you Max and Valko, learn something new every day.
"Max" wrote: 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(B10 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),B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(ISNUMBER(B1),B10,C1= "-"),"SPINS")))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "CT" wrote: 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
You have satisfied the 2nd condition
If B10 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 B10 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(B10,C1="Yes"),"SPINS TOL",IF(AND(B10,C1="-"),"SPINS")))) -- David Biddulph "CT" wrote in message ... 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
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" wrote in message ... 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
Thank you Rick, I did simplify my example and I will have other conditions
going forward. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
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" wrote in message ... Thank you Rick, I did simplify my example and I will have other conditions going forward. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
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. "David Biddulph" wrote: You have satisfied the 2nd condition If B10 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 B10 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(B10,C1="Yes"),"SPINS TOL",IF(AND(B10,C1="-"),"SPINS")))) -- David Biddulph "CT" wrote in message ... 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 B10 and C1="Yes" return "SPINS TOL" in A1. If B1="-" and C1="Yes" return "TOL" in A1 If B10 and C1="-" return "SPINS" in A1. Here is the formula I am using now. =IF(AND(B1="-",C1="-"),"No Match",IF(AND(B10,C1="Yes"),"SPINS TOL",IF(AND(B1="-",C1="Yes"),"TOL",IF(AND(B10,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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND Formula Problem
Welcome, CT
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "CT" wrote in message ... Thank you Max and Valko, learn something new every day. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula problem | Excel Discussion (Misc queries) | |||
| formula problem | New Users to Excel | |||
| formula problem | Excel Worksheet Functions | |||
| problem with a formula | Excel Discussion (Misc queries) | |||
| formula Problem | Excel Discussion (Misc queries) | |||
Linear Mode
