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.

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.

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.

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.

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.

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.