If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested IF(OR(AND) Statement with Two AND Conditions
I am using Excel 2003 and could use some assistance with a complex Nested IF
statement. Here is the criteria: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. I have tried several different variations of Nested IF statements, but to no avail. I do not know VBA, so this is not an option. Any help you can provide would be greatly appreciated. Thank you. |
#2
|
|||
|
|||
Nested IF(OR(AND) Statement with Two AND Conditions
A B C D E
1 Salesorg Salesdivision SalesOffice CustomerNbr Group 2 NAW1 N4 NWSA 90000000 BA The formula in cell E2 would be something like: =IF(AND(A2="NAW1",B2="N4",C2="NWSA"),IF(NOT(OR(D2= 90000000,D2=90000001,D2=90000002)),"UB","BA"),"Not Specified") Without the word wrap. |
#3
|
|||
|
|||
Nested IF(OR(AND) Statement with Two AND Conditions
"chiefs19" wrote:
IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. If Salesorg, Salesdivision, SalesOffice and CustomerNbr are named ranges, and Group is a parallel range of cells, you can write the following into the first Group cell and copy down: =if(AND(Salesorg="NAW1",Salesdivision="N4",SalesOf fice="NWSA"), if(OR(CustomerNbr={90000000,90000001,90000002}, "BA", "UB"), "") Alternatively, replace the name Salesorg, Salesdivision, SalesOffice and CustomerNbr with the proper cell references, for example: =if(AND(A2="NAW1",B2="N4"C2="NWSA"), if(OR(D2={90000000,90000001,90000002}, "BA", "UB"), "") Note that formula returns the null string ("") if A2, B2 or C2 is not the required condition, a case that you neglected to cover. ----- original message ----- "chiefs19" wrote: I am using Excel 2003 and could use some assistance with a complex Nested IF statement. Here is the criteria: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. I have tried several different variations of Nested IF statements, but to no avail. I do not know VBA, so this is not an option. Any help you can provide would be greatly appreciated. Thank you. |
#4
|
|||
|
|||
Nested IF(OR(AND) Statement with Two AND Conditions
I tried using this formula and I received "The formula you typed contains an
error message." The logical_test portion of the equation passes, but the value_if_true portion does not. I understand the logic of your equation and it will deliver the results I need if we can resolve the error. "Joe User" wrote: "chiefs19" wrote: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. If Salesorg, Salesdivision, SalesOffice and CustomerNbr are named ranges, and Group is a parallel range of cells, you can write the following into the first Group cell and copy down: =if(AND(Salesorg="NAW1",Salesdivision="N4",SalesOf fice="NWSA"), if(OR(CustomerNbr={90000000,90000001,90000002}, "BA", "UB"), "") Alternatively, replace the name Salesorg, Salesdivision, SalesOffice and CustomerNbr with the proper cell references, for example: =if(AND(A2="NAW1",B2="N4"C2="NWSA"), if(OR(D2={90000000,90000001,90000002}, "BA", "UB"), "") Note that formula returns the null string ("") if A2, B2 or C2 is not the required condition, a case that you neglected to cover. ----- original message ----- "chiefs19" wrote: I am using Excel 2003 and could use some assistance with a complex Nested IF statement. Here is the criteria: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. I have tried several different variations of Nested IF statements, but to no avail. I do not know VBA, so this is not an option. Any help you can provide would be greatly appreciated. Thank you. |
#5
|
|||
|
|||
Nested IF(OR(AND) Statement with Two AND Conditions
"chiefs19" wrote:
I tried using this formula and I received "The formula you typed contains an error message." My bad! Two typos in my previous posting. This time, I have copy-and-pasted from the Formula Bar (always a good idea): =IF(AND(A2="NAW1",B2="N4",C2="NWSA"), IF(OR(D2={90000000,90000001,90000002}), "BA", "UB"), "") ----- original message ----- "chiefs19" wrote: I tried using this formula and I received "The formula you typed contains an error message." The logical_test portion of the equation passes, but the value_if_true portion does not. I understand the logic of your equation and it will deliver the results I need if we can resolve the error. "Joe User" wrote: "chiefs19" wrote: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. If Salesorg, Salesdivision, SalesOffice and CustomerNbr are named ranges, and Group is a parallel range of cells, you can write the following into the first Group cell and copy down: =if(AND(Salesorg="NAW1",Salesdivision="N4",SalesOf fice="NWSA"), if(OR(CustomerNbr={90000000,90000001,90000002}, "BA", "UB"), "") Alternatively, replace the name Salesorg, Salesdivision, SalesOffice and CustomerNbr with the proper cell references, for example: =if(AND(A2="NAW1",B2="N4"C2="NWSA"), if(OR(D2={90000000,90000001,90000002}, "BA", "UB"), "") Note that formula returns the null string ("") if A2, B2 or C2 is not the required condition, a case that you neglected to cover. ----- original message ----- "chiefs19" wrote: I am using Excel 2003 and could use some assistance with a complex Nested IF statement. Here is the criteria: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. I have tried several different variations of Nested IF statements, but to no avail. I do not know VBA, so this is not an option. Any help you can provide would be greatly appreciated. Thank you. |
#6
|
|||
|
|||
Nested IF(OR(AND) Statement with Two AND Conditions
If have not had a chance to fully test it yet, but this seems to work. I have
never seen a formula structured this way. I can definitely use this to go back and shorten some of my current formulas. Thank you for all of your help!! "Joe User" wrote: "chiefs19" wrote: I tried using this formula and I received "The formula you typed contains an error message." My bad! Two typos in my previous posting. This time, I have copy-and-pasted from the Formula Bar (always a good idea): =IF(AND(A2="NAW1",B2="N4",C2="NWSA"), IF(OR(D2={90000000,90000001,90000002}), "BA", "UB"), "") ----- original message ----- "chiefs19" wrote: I tried using this formula and I received "The formula you typed contains an error message." The logical_test portion of the equation passes, but the value_if_true portion does not. I understand the logic of your equation and it will deliver the results I need if we can resolve the error. "Joe User" wrote: "chiefs19" wrote: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. If Salesorg, Salesdivision, SalesOffice and CustomerNbr are named ranges, and Group is a parallel range of cells, you can write the following into the first Group cell and copy down: =if(AND(Salesorg="NAW1",Salesdivision="N4",SalesOf fice="NWSA"), if(OR(CustomerNbr={90000000,90000001,90000002}, "BA", "UB"), "") Alternatively, replace the name Salesorg, Salesdivision, SalesOffice and CustomerNbr with the proper cell references, for example: =if(AND(A2="NAW1",B2="N4"C2="NWSA"), if(OR(D2={90000000,90000001,90000002}, "BA", "UB"), "") Note that formula returns the null string ("") if A2, B2 or C2 is not the required condition, a case that you neglected to cover. ----- original message ----- "chiefs19" wrote: I am using Excel 2003 and could use some assistance with a complex Nested IF statement. Here is the criteria: IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA. I have tried several different variations of Nested IF statements, but to no avail. I do not know VBA, so this is not an option. Any help you can provide would be greatly appreciated. Thank you. |
Thread Tools | |
Display Modes | |
|
|