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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
Thank you Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!