=IF(AND formula for calulating Impact and Probability
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
Best Answer

I believe the formula below should work for you.
A few things that I corrected were:
 Closing parenthesis for the AND() function were placed after the result rather after the last criteria.
 When using nested IF() statements, don't forget the closing parenthesis for each of the IF statements.
 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)))))))))
Answers

I believe the formula below should work for you.
A few things that I corrected were:
 Closing parenthesis for the AND() function were placed after the result rather after the last criteria.
 When using nested IF() statements, don't forget the closing parenthesis for each of the IF statements.
 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 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. 😁
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!