How do I : Evaluate 2 cells and show result based on combined values

I could use some help on formula development for the following condition. I have 2 columns of information and I want to show a result in a 3rd column based on based on the values in the other 2 columns. (see attached image for context)

  • If the value in the "ROAM status" column = resolved, then I would like the risk watch to be Green Check Symbol , regardless of the value in the "Risk Score" column.
  • If the value of the "ROAM status" column <> resolved , then I would like the value in the "Risk Watch" Column to be based on the value in the "Risk Score" column. (see below)
    • If value of "Risk Score" is greater than 15 then a Red X Symbol
    • If value of "Risk Score" is greater than 6 and less than 25, then a Yellow Exclamation Symbol.

I've tried combining the following formulas by various means, but I can not get them to work together. They work individually.

  • =IF(OR([ROAM Status]@row = "Resolved", [Risk Score]@row < 9), "Yes")
  • =IF(OR([ROAM Status]@row = "Resolved", [Risk Score]@row < 9), "Yes", "Hold")
  • =IF(AND([ROAM Status]@row <> "Resolved", [Risk Score]@row > 15), "No")


Best Answer

  • Mattisphere
    Mattisphere ✭✭✭✭✭
    Answer ✓

    You need nested IF statements. This assumes that you meant Red if the risk score was greater or equal to 15 and that if the status was <> "Resolved" but the risk score was less than 6, then you'd still want a green check.

    =IF([ROAM status]@row = "Resolved", "Yes", IF([Risk Score]@row >= 15, "No", IF(AND([Risk Score]@row < 15, [Risk Score]@row >= 6), "Hold", "Yes")))


Answers

  • Mattisphere
    Mattisphere ✭✭✭✭✭
    Answer ✓

    You need nested IF statements. This assumes that you meant Red if the risk score was greater or equal to 15 and that if the status was <> "Resolved" but the risk score was less than 6, then you'd still want a green check.

    =IF([ROAM status]@row = "Resolved", "Yes", IF([Risk Score]@row >= 15, "No", IF(AND([Risk Score]@row < 15, [Risk Score]@row >= 6), "Hold", "Yes")))


  • Thank you so much Mattisphere! It works as intended now! I just adjusted the values slightly to fit the exact conditions.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!