Nested IF(OR(AND) Statement with Two AND Conditions
in [Functions]
|
From: chiefs19 on 12 Apr 2010 15:20 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.
From: tompl on 12 Apr 2010 16:11 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"),"NotSpecified") Without the word wrap.
From: Joe User on 12 Apr 2010 17:26 "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",SalesOffice="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.
From: chiefs19 on 13 Apr 2010 09:02 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",SalesOffice="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.
From: Joe User on 13 Apr 2010 12:18
"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",SalesOffice="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. |