IF Statements with Multiple "Truths"

I am trying to build a nested IF statement that uses OR to look for multiple true options, but am getting an "Unparseable" error... any suggestions?


=IF(OR([ENG Risk]@row="Red", [ENG Risk]@row="Yellow"),"Risk", IF(OR([SC Risk]@row="Red", [SC Risk]@row="Yellow"),"Risk", IF(OR([$$$ Risk]@row="Red", [$$$ Risk]@row="Yellow"), "Risk", IF(OR([RES Risk]@row="Red", [RES Risk]@row="Yellow"), "Risk",))))

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @John E,

    That last comma is causing the error:

    If you delete that your formula will work, displaying Risk if any of the columns is red/yellow and blank otherwise. Alternatively give a result if they're all clear after the comma (e.g. "No risk") and that will also be fine.

    Hope this helps, any problems/questions just post! 🙂

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @John E ,

    You DO have an extra comma before your final 4 "))))"

    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • John E
    John E ✭✭

    I don't think the comma is extra... it is the separator between the "value if true" and the "value if false". nothing after the comma means it would leave the "false" blank... I think.

    I did try getting rid of the comma to see what would happen and the error still occurs, so that didn't fix the issue.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    If you wanted a blank "value if false", then you would use "" (speech marks with nothing between), but you can omit this entirely to get the same result.

    Not entirely sure what would be throwing an error with the comma removed, as a testing the formula without the extra comma shows it seems to work fine:

    Does the formula work on any rows if you convert it to cell rather than column (and therefore can see some rows which give errors)?

  • John E
    John E ✭✭

    I went through and reset all of the "links" in the formula to the cells and it works now! Not sure what that was all about, but thanks everyone!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!