formula to automate a status not working

axelrodo

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 push-ups 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!

Kelly Moore

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

axelrodo

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?

Kelly Moore

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

Kelly Moore

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

axelrodo

Genius!!! Many thanks! Saved my day!

axelrodo

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?

Kelly Moore

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

axelrodo

Hi Kelly, yes, exactly, thank you, I have now a working risk assessment management sheet working!