A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Nested IF(OR(AND) Statement with Two AND Conditions



 
 
Thread Tools Display Modes
  #1  
Old April 12th, 2010, 08:20 PM posted to microsoft.public.excel.worksheet.functions
chiefs19
external usenet poster
 
Posts: 3
Default 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  
Old April 12th, 2010, 09:11 PM posted to microsoft.public.excel.worksheet.functions
TomPl
external usenet poster
 
Posts: 239
Default 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  
Old April 12th, 2010, 10:26 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 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  
Old April 13th, 2010, 02:02 PM posted to microsoft.public.excel.worksheet.functions
chiefs19
external usenet poster
 
Posts: 3
Default 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  
Old April 13th, 2010, 05:18 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 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  
Old April 14th, 2010, 02:55 PM posted to microsoft.public.excel.worksheet.functions
chiefs19
external usenet poster
 
Posts: 3
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.