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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!