Risk Level Formula

Hazel A.

Hello,

I am trying to calculate risk level automatically (by displaying RYG balls) based on the following:

Risk Level is Green If I get one of the following combinations:

Impact = Low AND Probability = Not Likely
Impact = Low AND Probability = Likely
Impact = Med AND Probability = Not Likely

Risk Level is Yellow If I get one of the following combinations:

Impact = Low AND Probability = Very Likely
Impact = Med AND Probability = Likely
Impact = High AND Probability = Not Likely

Risk Level is Red If I get one of the following combinations:

Impact = Med AND Probability = Very Likely
Impact = High AND Probability = Likely
Impact = High AND Probability = Very Likely

I tried inputting the formula below. Not really sure how to close an IF AND Formula, I also think my logic is wrong in some parts. I got "Unparseable" as an error.

=IF(AND(Impact2 = "Low", Probability2 = "Not Likely", Level2 = "Green", IF(AND(Impact2 = "Low", Probability2 = "Likely", Level2 = "Green", IF(AND(Impact2 = "Low", Probability2 = "Very Likely", Level2 = "Green", IF(AND(Impact2 = "Med", Probability2 = "Not Likely", Level2 = "Green", IF(AND(Impact 2 = "Med", Probability2 = "Likely", Level2 = "Yellow", IF(AND(Impact2 = "Med", Probability2 = "Very Likely", Level2 = "Red", IF(AND(Impact2 = "High", Probability2 = "Not Likely", Level2 = "Yellow", IF(AND(Impact2 = "High", Probability2 = "Likely", Level2 = "Red", IF(AND(Impact2 = "High", Probability2 = "Very Likely", Level2 = "Red", "Yellow")))))))))))

I have one column for Impact, another column for probability, and a third column for Risk Level.

Thank you in advance for the help!

Find more posts tagged with

Project Management

Comments

Mike Wilday

You're close... but if you put this formula in the level 2 column cell then you don't need to state Level 2 =...

Also.. An AND statement needs to be closed within the IF statement it resides. Try this.

=IF(AND(Impact2 = "Low", Probability2 = "Not Likely"), "Green", IF(AND(Impact2 = "Low", Probability2 = "Likely"), "Green", IF(AND(Impact2 = "Low", Probability2 = "Very Likely"), "Green", IF(AND(Impact2 = "Med", Probability2 = "Not Likely"), "Green", IF(AND(Impact 2 = "Med", Probability2 = "Likely"), "Yellow", IF(AND(Impact2 = "Med", Probability2 = "Very Likely"), "Red", IF(AND(Impact2 = "High", Probability2 = "Not Likely"), "Yellow", IF(AND(Impact2 = "High", Probability2 = "Likely"), "Red", IF(AND(Impact2 = "High", Probability2 = "Very Likely"), "Red", "Yellow")))))))))

Hazel A.

Hi Mike,

Thanks for the correction! Formula works now

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…