Solved

Excel, IF statement with AND, OR - Two Columns. Nine Combinations

Posted on 2012-03-23
6
377 Views
Last Modified: 2012-03-26
We need to identify NAFTA shipments, with a formula inserted into a spreadsheet.
I have tried a number of IF statements with OR and AND, but I cannot get it to work.

We have:

3 countries = 9 combinations

Column A = Origin Country
Column B = Destination Country

What we are trying to do:

If the value in A1 = USA, MEX, or CAN
and the value in B1 = USA, MEX, or CAN, then C1 returns "NAFTA".


IF(OR(A1="USA",A1="MEX",A1=CAN")

AND

IF(OR(B1="USA",B1="MEX",B1="CAN")

then, "NAFTA"

otherwise, "" )

Any advise that you can provide is appreciated.

Tosagua
0
Comment
Question by:Tosagua
6 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 50 total points
ID: 37758176
=IF(AND(OR(A1="MEX",A1 = "CAN",A1="USA"),OR(B1="MEX",B1 = "CAN",B1="USA")),"NAFTA","")
0
 

Author Comment

by:Tosagua
ID: 37758326
als315,

I see now, how this should work, but it doesn't.
It always returns "FALSE", regardless of the combination.
 See attached.

Tosagua
0
 
LVL 1

Assisted Solution

by:coogsirc
coogsirc earned 50 total points
ID: 37758375
I created a range called "nft" with the 3 country codes and plugged this formula into C1.
=IF(AND(MATCH(A1,nft,0),MATCH(B1,nft,0)),"nafta")

If a1 or b1 isn't in the list c1 returns #N/A.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 81

Accepted Solution

by:
byundt earned 400 total points
ID: 37759603
If both countries are the same, I assume the answer should not be NAFTA. If so, then you might consider a formula like:
=IF((A2<>B2)*(SUMPRODUCT(--(A2:B2={"USA";"MEX";"CAN"}))=2),"NAFTA","")

Brad
NAFTA-Q27646241.xlsx
0
 
LVL 40

Expert Comment

by:als315
ID: 37760075
An example with equation from my comment
NAFTA.xls
0
 

Author Closing Comment

by:Tosagua
ID: 37767044
Another learning experience.

I appreciate everyone's help, however 'byundt' saved me from making a simple mistake (USA to USA is not NAFTA).

Part of the difficuly was that the cells were formatted as text, and when the format was changed to general, there was srtill no change.

Fortunately, I found this comment on EE,

"by: bouscal Posted on 2010-12-13 at 09:34:43ID: 34336238 Rank: Master
 
After you change the formatting to General from Text you may also need to select the cell, hit F2 to activate it then Enter for it to calculate.  Excel doesn't always like to apply formatting.

Thank you for all of your assistance.

Tosagua
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Outlook Free & Paid Tools
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question