=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
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!