formula to automate a status not working
Hello,
I'm working on a risk assessment matrix with an Impact column with 5 options (e.g. Insignificant, Minor, Moderate, Major and Extreme) and a Likelihood column with 5 options (e.g. Rare, Unlikely, Possible, Likely, Almost certain). When selecting an option in the Impact column and then one in the Likelihood one, it should normally return one of the following Ratings: Very low, Low, Medium, High, Very high.
The formula I've written is the following:
=IF(AND(Likelihood16 = "Rare", Impact16 = "Insignificant"), "Very low", IF(Likelihood16 = "Rare", Impact16 = "Minor"), "Very low", IF(AND(OR(Likelihood16 = "Rare"), Impact16 = "Moderate"), "Low", IF(AND(OR(Likelihood16 = "Rare"), Impact16 = "Major"), "Medium", IF(AND(OR(Likelihood16 = "Rare"), Impact16 = "Extreme"), "Medium", IF(AND(OR(Likelihood16 = "Unlikely"), Impact16 = "Insignificant"), "Very low", IF(AND(OR(Likelihood16 = "Unlikely"), Impact16 = "Low"), "Minor", IF(AND(OR(Likelihood16 = "Unlikely"), Impact16 = "Medium"), "Moderate", IF(AND(OR(Likelihood16 = "Unlikely"), Impact16 = "Major"), "Medium", IF(AND(OR(Likelihood16 = "Unlikely"), Impact16 = "Extreme"), "High", IF(AND(OR(Likelihood16 = "Possible"), Impact16 = "Insignificant"), "Low", IF(AND(OR(Likelihood16 = "Possible"), Impact16 = "Minor"), "Medium", IF(AND(OR(Likelihood16 = "Possible"), Impact16 = "Moderate"), "Medium", IF(AND(OR(Likelihood16 = "Possible"), Impact16 = "Major"), "High", IF(AND(OR(Likelihood16 = "Possible"), Impact16 = "Extreme"), "High", IF(AND(OR(Likelihood16 = "Likely"), Impact16 = "Insignificant"), "Medium", IF(AND(OR(Likelihood16 = "Likely"), Impact16 = "Minor"), "Medium", IF(AND(OR(Likelihood16 = "Likely"), Impact16 = "Moderate"), "High", IF(AND(OR(Likelihood16 = "Likely"), Impact16 = "Major"), "High", IF(AND(OR(Likelihood16 = "Likely"), Impact16 = "Extreme"), "Very high", IF(AND(OR(Likelihood16 = "Almost certain"), Impact16 = "Insignificant"), "Medium", IF(AND(OR(Likelihood16 = "Almost certain"), Impact16 = "Minor"), "Medium", IF(AND(OR(Likelihood16 = "Almost certain"), Impact16 = "Moderate"), "High", IF(AND(OR(Likelihood16 = "Almost certain"), Impact16 = "Major"), "Very high", IF(AND(OR(Likelihood16 = "Almost certain"), Impact16 = "Extreme "), "Very high")))
and...it returns a blank...nothing...
The way I wrote the above formula is in expanding the one I've picked from another template but which has twice a Likelihood option when I only will have one:
=IF(AND(OR(Likelihood16 = "Rare", Likelihood16 = "Unlikely"), Impact16 = "Insignificant"), "Very low", IF(AND(OR(Likelihood16 = "Possible", Likelihood16 = "Likely"), Impact16 = "Minor"), "Medium", IF(AND(OR(Likelihood16 = "Almost certain", Likelihood16 = "Almost certain"), Impact16 = "Extreme"), "Very high")))
I've tried removing once the AND keeping the OR, removing the OR and keeping the AND, taking a walk to let some steam off, trying again without the AND and the OR, doing a few pushups and moving some furniture to release some frustrations, tried again ensuring no extra space was hiding in some wrong place, to no avail...
If anyone could help, I'd be immensely grateful!
Best Answers

Hey @axelrodo
Try this.
=IF(AND(Likelihood@row = "Rare", Impact@row = "Insignificant"), "Very low", IF(AND(Likelihood@row = "Rare", Impact@row = "Minor"), "Very low", IF(AND(Likelihood@row = "Rare", Impact@row = "Moderate"), "Low", IF(AND(Likelihood@row = "Rare", Impact@row = "Extreme"), "Medium", IF(AND(Likelihood@row = "Rare", Impact@row = "Major"), "Medium", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Insignificant"), "Very low", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Low"), "Minor", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Medium"), "Moderate", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Extreme"), "High", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Major"), "Medium", IF(AND(Likelihood@row = "Possible", Impact@row = "Insignificant"), "Low", IF(AND(Likelihood@row = "Possible", Impact@row = "Minor"), "Medium", IF(AND(Likelihood@row = "Possible", Impact@row = "Moderate"), "Medium", IF(AND(Likelihood@row = "Possible", Impact@row = "Major"), "High", IF(AND(Likelihood@row = "Possible", Impact@row = "Extreme"), "High", IF(AND(Likelihood@row = "Likely", Impact@row = "Insignificant"), "Medium", IF(AND(Likelihood@row = "Likely", Impact@row = "Minor"), "Medium", IF(AND(Likelihood@row = "Likely", Impact@row = "Moderate"), "High", IF(AND(Likelihood@row = "Likely", Impact@row = "Major"), "High", IF(AND(Likelihood@row = "Likely", Impact@row = "Extreme"), "Very high", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Minor"), "Medium", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Insignificant"), "Medium", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Moderate"), "High", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Major"), "Very high", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Extreme"), "Very high")))))))))))))))))))))))))
We could combine your AND's into OR(AND)s but that makes the formula more difficult to read and, unless you are exceeding the number of characters in cell (which you are not), there didn't seem to be a point. Often I will drop a formula like this into Word so I can read it more easily  especially if I wanted to count that I add five of each category (you do, I counted).
Kelly

Hi Kelly, 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...
I've tried copying your formula but got stuck with the OK TO PROCEED trigger as it can be triggered by two options Very low or Low so tried a mix of yours and mine before and it got cryptic...what am I doing wrong?

Hey
Is this what you are looking for?
=IF(OR(Impact@row = "Select", Likelihood@row = "Select"), "SELECT RISK SEVERITY AND LIKELIHOOD", IF(OR(Rating@row = "Very low", Rating@row = "Low"), "OK TO PROCEED", IF(Rating@row = "Medium", "TAKE MITIGATION EFFORTS", IF(Rating@row = "High", "SEEK SUPPORT", IF(Rating@row = "Very high", "PUT EVENT ON HOLD")))))
I took the AND/OR combinations out of the formula you proposed. The AND/OR is used when you have multiple conditions that must be met within the same IF statement and within those conditions, multiple choices (the OR) exists). From what you wrote, I didn't see any requirements for the AND function.
Kelly
Answers

Hey @axelrodo
Try this.
=IF(AND(Likelihood@row = "Rare", Impact@row = "Insignificant"), "Very low", IF(AND(Likelihood@row = "Rare", Impact@row = "Minor"), "Very low", IF(AND(Likelihood@row = "Rare", Impact@row = "Moderate"), "Low", IF(AND(Likelihood@row = "Rare", Impact@row = "Extreme"), "Medium", IF(AND(Likelihood@row = "Rare", Impact@row = "Major"), "Medium", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Insignificant"), "Very low", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Low"), "Minor", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Medium"), "Moderate", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Extreme"), "High", IF(AND(Likelihood@row = "Unlikely", Impact@row = "Major"), "Medium", IF(AND(Likelihood@row = "Possible", Impact@row = "Insignificant"), "Low", IF(AND(Likelihood@row = "Possible", Impact@row = "Minor"), "Medium", IF(AND(Likelihood@row = "Possible", Impact@row = "Moderate"), "Medium", IF(AND(Likelihood@row = "Possible", Impact@row = "Major"), "High", IF(AND(Likelihood@row = "Possible", Impact@row = "Extreme"), "High", IF(AND(Likelihood@row = "Likely", Impact@row = "Insignificant"), "Medium", IF(AND(Likelihood@row = "Likely", Impact@row = "Minor"), "Medium", IF(AND(Likelihood@row = "Likely", Impact@row = "Moderate"), "High", IF(AND(Likelihood@row = "Likely", Impact@row = "Major"), "High", IF(AND(Likelihood@row = "Likely", Impact@row = "Extreme"), "Very high", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Minor"), "Medium", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Insignificant"), "Medium", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Moderate"), "High", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Major"), "Very high", IF(AND(Likelihood@row = "Almost certain", Impact@row = "Extreme"), "Very high")))))))))))))))))))))))))
We could combine your AND's into OR(AND)s but that makes the formula more difficult to read and, unless you are exceeding the number of characters in cell (which you are not), there didn't seem to be a point. Often I will drop a formula like this into Word so I can read it more easily  especially if I wanted to count that I add five of each category (you do, I counted).
Kelly

Genius!!! Many thanks! Saved my day!

Hi Kelly, 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...
I've tried copying your formula but got stuck with the OK TO PROCEED trigger as it can be triggered by two options Very low or Low so tried a mix of yours and mine before and it got cryptic...what am I doing wrong?

Hey
Is this what you are looking for?
=IF(OR(Impact@row = "Select", Likelihood@row = "Select"), "SELECT RISK SEVERITY AND LIKELIHOOD", IF(OR(Rating@row = "Very low", Rating@row = "Low"), "OK TO PROCEED", IF(Rating@row = "Medium", "TAKE MITIGATION EFFORTS", IF(Rating@row = "High", "SEEK SUPPORT", IF(Rating@row = "Very high", "PUT EVENT ON HOLD")))))
I took the AND/OR combinations out of the formula you proposed. The AND/OR is used when you have multiple conditions that must be met within the same IF statement and within those conditions, multiple choices (the OR) exists). From what you wrote, I didn't see any requirements for the AND function.
Kelly

Hi Kelly, yes, exactly, thank you, I have now a working risk assessment management sheet working!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!