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

Tags:

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers
    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

  • Paul Newcome
    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?

    thinkspi.com

  • David Tutwiler
    David Tutwiler Overachievers

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

    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
    David Tutwiler Overachievers
    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"

  • BTMarketingOps
    BTMarketingOps ✭✭✭✭✭✭

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

  • BTMarketingOps
    BTMarketingOps ✭✭✭✭✭✭

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

  • David Tutwiler
    David Tutwiler Overachievers

    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
    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
    David Tutwiler Overachievers

    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.
    Genevieve P. Employee Admin

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

    Cheers,

    Genevieve

  • David Tutwiler
    David Tutwiler Overachievers

    Thank you Genevieve