IF function with OR to return a value of 0 or 1

Hi,

I'm trying to set a value of 1 or 0 in a field depending on the value in a field called Communication Language. The field can have any or all of these values. I keep getting an #UNPARSEABLE error and I can't figure out what it doesn't like. I want it to have a value of 1 if it is any language other than these.

=IF([Communication Language]@row ="French" , "1",

IF [Communication Language]@row ="German", "1",

IF([Communication Language]@row = "Spanish", "1",

IF([Communication Language]@row = "Korean", "1",

IF([Communication Language]@row = "Japanese", "1",

IF([Communication Language]@row = "Chinese (Simplified)", "1",

IF([Communication Language]@row = "Chinese (Traditional)", "1", "0")

It is probably obvious, but I can't see the issue. Maybe I have looked at it for too long.

Thanks for your help!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @sackerman70

    With a multi-select cell, if you search for = "value" then it will only be true if that one value is in the cell. That means that to search for English, you can use the same structure as your current formula but change the output from being 1 to being the text you want, like so:

    =IF([Communication Language]@row = "English", "English Only",


    Then you can look to see if that cell HAS English along with anything else, and use that to say "translated":

    IF(HAS([Communication Language]@row, "English"), "Translated",


    Since the formula will only move on to the second statement if the first statement is FALSE (or in this case, if the cell is not only "English"), then you don't have to write anything else in the logic. For your final statement, you could return something that shows none of the selections are English... perhaps:

    =IF([Communication Language]@row = "English", "English Only", IF(HAS([Communication Language]@row, "English"), "Translated", "No English"))


    If I've misunderstood and you're only looking for "english only" and "all other possible selections", then you can simplify it by only having the first statement and the alternative text:

    =IF([Communication Language]@row = "English", "English Only", "Translated")


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @sackerman70

    I hope you're well and safe!

    Try something like this.

    =IF([Communication Language]@row =French , 1,

    IF [Communication Language]@row =German, 1,

    IF([Communication Language]@row = Spanish, 1,

    IF([Communication Language]@row = Korean, 1,

    IF([Communication Language]@row = Japanese, 1,

    IF([Communication Language]@row = Chinese (Simplified), 1,

    IF([Communication Language]@row = Chinese (Traditional), 1, 0)

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thank you Andree! I am still getting the unparseable error. Does it matter that the field I am reference for Communication Language is a drop down field type? I noticed when I put in the formula that some pieces are red, but I wasn't able to find any information on why that would be. I thought maybe it was trying to show me what it doesn't like. Everything is spelled right. I tried putting " around the language values to see if that was the issue, but still got the error.


  • I was able to get my first formula to work finally. I used the following:

    =IF([Communication Language]@row = "French", 1, IF([Communication Language]@row = "German", 1, IF([Communication Language]@row = "Spanish", 1, IF([Communication Language]@row = "Korean", 1, IF([Communication Language]@row = "Japanese", 1, IF([Communication Language]@row = "Chinese (Simplified)", 1, IF([Communication Language]@row = "Chinese (Traditional)", 1, 0)))))))

    However, now realize that it isn't doing what I actually want. The Communication Language field is a drop down list that allows multiple values in the cell. So a cell could have English, French or English, French, Spanish, Japanese, Korean, German or any combination of the 8 languages. I have tried introducing an OR within the IF statement, but it just breaks it the formula and I get an error. Is it possible in Smartsheet to use an IF statement like this?

    The ideal scenario would be to have a result of "English Only" if the only value is English and then if there is more than English have a result of "Translated".

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @sackerman70

    With a multi-select cell, if you search for = "value" then it will only be true if that one value is in the cell. That means that to search for English, you can use the same structure as your current formula but change the output from being 1 to being the text you want, like so:

    =IF([Communication Language]@row = "English", "English Only",


    Then you can look to see if that cell HAS English along with anything else, and use that to say "translated":

    IF(HAS([Communication Language]@row, "English"), "Translated",


    Since the formula will only move on to the second statement if the first statement is FALSE (or in this case, if the cell is not only "English"), then you don't have to write anything else in the logic. For your final statement, you could return something that shows none of the selections are English... perhaps:

    =IF([Communication Language]@row = "English", "English Only", IF(HAS([Communication Language]@row, "English"), "Translated", "No English"))


    If I've misunderstood and you're only looking for "english only" and "all other possible selections", then you can simplify it by only having the first statement and the alternative text:

    =IF([Communication Language]@row = "English", "English Only", "Translated")


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Some things to watch out for based on your last screenshot:

    You definitely need the quotes around the languages (or any other text string in a formula).

    The space after the second IF should be removed.

    IF (

    vs

    IF(

  • Thank you for your help everyone! Your suggestion was exactly what I needed Genevieve.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!