=IF(AND formula for calulating Impact and Probability

Shajan

I am trying to plug this matrix into a smartsheet:

I'm pretty new to formulas but I am trying to construct an IF(AND formula. Below is what I have constructed.

=IF(AND([Impact]@row "Low 1" , [Probability]@row "Low 1" , "1"), 

IF(AND([Impact]@row "Low 1" , [Probability]@row "Medium 3" , "3"), 

IF(AND([Impact]@row "Low 1" , [Probability]@row "High 5" , "5"), 

IF(AND([Impact]@row "Medium 3" , [Probability]@row "Low 1" , "3"), 

IF(AND([Impact]@row "Medium 3" , [Probability]@row "Medium 3" , "9"), 

IF(AND([Impact]@row "Medium 3" , [Probability]@row "High 5" , "15"), 

IF(AND([Impact]@row "High 5" , [Probability]@row "Low 1" , "5"), 

IF(AND([Impact]@row "High 5" , [Probability]@row "Medium 3" , "15"), 

IF(AND([Impact]@row "High 5" , [Probability]@row "High 5" , "25"), 

It basically reads as IF the IMPACT column is A and Probability column is B THEN the answer is X.

But what I have above comes out as unparsable in smartsheet ... I don't know how to format it properly.

Later on I plan to add a Severity column that says if Score is less than 5 it is "LOW" IF Score is Greater than 5 and Less than 15 it is "Medium" If Score is greater than 15 it is "High".

Any suggestions?

Thanks

Jason Duryea

I believe the formula below should work for you.

A few things that I corrected were:

1. Closing parenthesis for the AND() function were placed after the result rather after the last criteria.
2. When using nested IF() statements, don't forget the closing parenthesis for each of the IF statements.
3. Equal signs were missing from the criteria within the AND functions.

=IF(AND(Impact@row = "Low 1", Probability@row = "Low 1"), 1, IF(AND(Impact@row = "Low 1", Probability@row = "Medium 3"), 3, IF(AND(Impact@row = "Low 1", Probability@row = "High 5"), 5, IF(AND(Impact@row = "Medium 3", Probability@row = "Low 1"), 3, IF(AND(Impact@row = "Medium 3", Probability@row = "Medium 3"), 9, IF(AND(Impact@row = "Medium 3", Probability@row = "High 5"), 15, IF(AND(Impact@row = "High 5", Probability@row = "Low 1"), 5, IF(AND(Impact@row = "High 5", Probability@row = "Medium 3"), 15, IF(AND(Impact@row = "High 5", Probability@row = "High 5"), 25)))))))))

Jason Duryea

I believe the formula below should work for you.

A few things that I corrected were:

1. Closing parenthesis for the AND() function were placed after the result rather after the last criteria.
2. When using nested IF() statements, don't forget the closing parenthesis for each of the IF statements.
3. Equal signs were missing from the criteria within the AND functions.

=IF(AND(Impact@row = "Low 1", Probability@row = "Low 1"), 1, IF(AND(Impact@row = "Low 1", Probability@row = "Medium 3"), 3, IF(AND(Impact@row = "Low 1", Probability@row = "High 5"), 5, IF(AND(Impact@row = "Medium 3", Probability@row = "Low 1"), 3, IF(AND(Impact@row = "Medium 3", Probability@row = "Medium 3"), 9, IF(AND(Impact@row = "Medium 3", Probability@row = "High 5"), 15, IF(AND(Impact@row = "High 5", Probability@row = "Low 1"), 5, IF(AND(Impact@row = "High 5", Probability@row = "Medium 3"), 15, IF(AND(Impact@row = "High 5", Probability@row = "High 5"), 25)))))))))

Shajan

@Jason Duryea Thank you so much for the answer. I had been contemplating ways to reformulate the query. But I'll likely use what you have provided as an answer instead of changing things. 😁