formula to automate a return not working (2)

Hi,

I'm stuck once again with a slightly different formula, maybe you could help?

Following up from the above set of possibilities, the ACTION column should return one of the following options:

SELECT RISK SEVERITY - when no option has been previously entered (=default setting)

OK TO PROCEED (if risk severity is either Very low or Low)

TAKE MITIGATION EFFORTS (if risk severity is Medium)

SEEK SUPPORT (if risk severity is High)

PUT EVENT ON HOLD (if risk severity is Very high)

and so the formula I have entered is:

=IF(OR(Impact16 = "-Select-", Likelihood16 = "-Select-"), "SELECT RISK SEVERITY AND LIKELIHOOD", IF(AND(OR(Rating16 = "Very low", Rating16 = “Low”) "OK TO PROCEED", IF(Rating16 = "Medium", "TAKE MITIGATION EFFORTS", IF(Rating16 = "High", "SEEK SUPPORT", IF(Rating16 = “Very high”, "PUT EVENT ON HOLD"))))

and it says Unparseable...

What am I doing wrong?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You have a few syntax issues.

    First you need to decide whether you want the AND function or the OR function prior to "Ok to proceed". You also need to put a comma after you close out the function and before the corresponding output.

    Next (and the reason for this error), you have what are called "smart quotes" in your formula which (ironically enough) Smartsheet does not recognize. Notice how some of them are slanted to show open vs closed and some are straight up and down? You want them all to be straight up and down. Retype it in a program such as notepad (not Word), here in the community, or directly in the sheet itself to correct these.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You have a few syntax issues.

    First you need to decide whether you want the AND function or the OR function prior to "Ok to proceed". You also need to put a comma after you close out the function and before the corresponding output.

    Next (and the reason for this error), you have what are called "smart quotes" in your formula which (ironically enough) Smartsheet does not recognize. Notice how some of them are slanted to show open vs closed and some are straight up and down? You want them all to be straight up and down. Retype it in a program such as notepad (not Word), here in the community, or directly in the sheet itself to correct these.

  • Hi Paul,

    I've followed your instructions and put this one in:

    =IF(OR(Impact16 = "-Select-", Likelihood16 = "-Select-"), "SELECT RISK SEVERITY AND LIKELIHOOD", IF(OR(Rating16 = "Very low", Rating16 = "Low"), "OK TO PROCEED", IF(Rating16 = "Medium", "TAKE MITIGATION EFFORTS", IF(Rating16 = "High", "SEEK SUPPORT", IF(Rating16 = "Very high", "PUT EVENT ON HOLD"))))

    and it works perfectly! Thanks heaps! The devil is in the detail obviously ;-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!