Another Nested IF Formula
Could use your help again. Not sure why I have so much trouble with these nested IF statements but I do! When I use the formula below and the Business Unit is equal to EMEA, it returns ASD....any ideas?
=IF([Business Unit]@row = "Biologics", "ASD", IF([Business Unit]@row = "Western", "ASD", IF([Business Unit]@row = "Simple Plex", "ASD", IF([Business Unit]@row <> "Immunoassay", "ASD", IF([Business Unit]@row = "Antibodies", "RSD", IF([Business Unit]@row = "Proteins", "RSD", IF([Business Unit]@row = "Cell & Gene Therapy", "RSD", IF([Business Unit]@row = "Small Molecules", "RSD", IF([Business Unit]@row = "Asuragen", "MDD", IF([Business Unit]@row = "EMEA", "EMEA"))))))))))
Best Answer
-
<> does not mean contains it means Not Equal To. If you want to use contains you'll need to use the wrapper:
IF(CONTAINS("Immunoassay", [Business Unit]@row), "ASD"
Answers
-
Are you able to provide a screenshot of the formula actually in the sheet as well as a screenshot of the row where it is outputting the wrong text?
-
I think you have a typo at this part of the formula:
IF([Business Unit]@row <> "Immunoassay"
I think this should be IF([Business Unit]@row = "Immunoassay"
What's happening is it gets to that part of the formula and says, "@row is not equal to Immunoassay, therefore I need to put down ASD".
Also, you could simplify your calls with an OR. Something like:
=IF(OR([Business Unit]@row = "Biologics", [Business Unit]@row = "Western", [Business Unit]@row = "Simple Plex", [Business Unit]@row = "Immunoassay"), "ASD"
-
Attached is a screen shot of the row that is giving me the incorrect result. The reason I have <> in front of Immunoassay is because it is actually Immunoassay (ELISA/Luminex) but the formula gets confused with the () in that so I used the carrots to mean contains Immunoassay. Hope that makes sense.
Here is the for
mula that is in that row:
=IF([Business Unit]@row = "Biologics", "ASD", IF([Business Unit]@row = "Western", "ASD", IF([Business Unit]@row = "Simple Plex", "ASD", IF([Business Unit]@row <> "Immunoassay", "ASD", IF([Business Unit]@row = "Antibodies", "RSD", IF([Business Unit]@row = "Proteins", "RSD", IF([Business Unit]@row = "Cell & Gene Therapy", "RSD", IF([Business Unit]@row = "Small Molecules", "RSD", IF([Business Unit]@row = "Asuragen", "MDD", IF([Business Unit]@row = "EMEA", "EMEA"))))))))))
-
<> does not mean contains it means Not Equal To. If you want to use contains you'll need to use the wrapper:
IF(CONTAINS("Immunoassay", [Business Unit]@row), "ASD"
-
@Paul Newcome Did I post what you were looking for?
-
@Paul Newcome @David Tutwiler Never mind, David's last comment did solve my issue! Thanks David!
-
No problem. Glad it's working for you. Do you mind marking that as the answer for anyone who might come along this post in the future? It will move that up towards the top so they can see it.
-
@David Tutwiler I accidentally clicked on No for Did it answer the question. Is there any way for you to undo that?
-
I can't but I'll try and tag some folks from Smartsheet who might be able to. No worries and glad you got your formula working.
-
Hiya! Adjusted! Thanks for keeping the Community up-to-date.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!