IF AND with combination of does not equal and does equal
Hi I have 3 columns all containing country names.
For each row, I want to do a nested statement to show:
If all 3 countries (columns) = "UAE" then put "Local Servicing - UAE"
*If all 3 countries are the same then put "Local Servicing"
*If first 2 columns are UAE but column 3 is not then put "HUB"
otherwise put "HUB & Spoke",
I had some of it working but can't get those with an asterix to work? Any ideas please?:
Below doesn't work?
=IF(LEVEL@row = "DK", IF(AND([Servicing Country]@row = "UAE", [Fulfilment Country]@row = "UAE", [Employee Country]@row = "UAE"), "LOCAL SERVICING - UAE"), IF(AND([Employee Country]@row <> "UAE", [Servicing Country]@row = "UAE", [Fulfilment Country]@row = "UAE"), "HUB"))
Answers
-
I am a bit confused by the logic. It is important to have it clear before you start developing the IF statement.
I don't know what First2 columns are related to [Fulfilment Country], [Employee Country],[Servicing Country] and there is no mention of LEVEL
I have looked at your logic and think I understand it this way.
It is best to rule out conditions first and then try to account for them all at the end
- If Column1 and Column2 are not the same then you always have "HUB and Spoke"
- If Column3 does not the same as Column1 (or 2) then we have "HUB"
- If Column 3 is UAE then you have "Local Servicing - UAE" otherwise "Local Servicing"
Try this
=IF(NOT(Column1@row = Column2@row),"Hub & Spoke",IF(NOT(Column1@row = Column3@row),"HUB",IF(Column3@row = "UAE","Local Servicing - UAE","Local Servicing")))
Breaking it down
First Check for Different Countries in Column1 and Column2
IF(NOT(Column1@row = Column2@row),"Hub & Spoke",
Second Check that Column3 doesn't match Column2 (We know Column1 = Column2 at this point so it is arbitrary which one we pick)
IF(NOT(Column1@row = Column3@row),"HUB",
Third Check for UAE (as we now know Col3 is the same as Col1 and Col2)
IF(Column3@row = "UAE","Local Servicing - UAE","Local Servicing")))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!