Another Nested IF Formula

BTMarketingOps

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"))))))))))

David Tutwiler

<> 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

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?

David Tutwiler

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"

BTMarketingOps

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

Capture.JPG

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"))))))))))

David Tutwiler

<> 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"

BTMarketingOps

@Paul Newcome Did I post what you were looking for?

BTMarketingOps

@Paul Newcome @David Tutwiler Never mind, David's last comment did solve my issue! Thanks David!

David Tutwiler

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.

BTMarketingOps

@David Tutwiler I accidentally clicked on No for Did it answer the question. Is there any way for you to undo that?

David Tutwiler

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.

@Maxwell Griffith @Genevieve P.

Genevieve P.

Hiya! Adjusted! Thanks for keeping the Community up-to-date.

Cheers,

Genevieve

David Tutwiler

Thank you Genevieve