Risk and Opportunity Matrix - IF/AND Statements

Options
sophiaashepard
sophiaashepard ✭✭✭
edited 06/26/23 in Formulas and Functions

Hello,

I am trying to utilize a Risk & Opportunity Matrix to assign priority levels to risks and opportunities. I am using this formula to try to do this but I keep getting an #INCORRECT ARGUMENT. Does anyone know why? I even tried a test with only one IF/AND statement to see if it works and I still get the same message. Is it because there are parentheses in the text statements?

=IF(AND([Risk Impact Severity]@row= "Critical", [Risk Probability of Occurence]@row="Very Likely (> 75%)", "Critical", IF(AND([Risk Impact Severity]@row= "Critical", [Risk Probability of Occurence]@row="Probable (51% - 75%)", "Critical", IF(AND([Risk Impact Severity]@row= "Critical", [Risk Probability of Occurence]@row="Possible (26% - 50%)", "Critical", IF(AND([Risk Impact Severity]@row= "Critical", [Risk Probability of Occurence]@row="Not to be ruled out (5% - 25%)", "Critical", IF(AND([Risk Impact Severity]@row= "Critical", [Risk Probability of Occurence]@row="Very unlikely (< 5%)", "Important", IF(AND([Risk Impact Severity]@row= "Major", [Risk Probability of Occurence]@row="Very Likely (> 75%)", "Critical", IF(AND([Risk Impact Severity]@row= "Major", [Risk Probability of Occurence]@row="Probable (51% - 75%)", "Critical", IF(AND([Risk Impact Severity]@row= "Major", [Risk Probability of Occurence]@row="Possible (26% - 50%)", "Critical", IF(AND([Risk Impact Severity]@row= "Major", [Risk Probability of Occurence]@row="Not to be ruled out (5% - 25%)", "Important", IF(AND([Risk Impact Severity]@row= "Major", [Risk Probability of Occurence]@row="Very unlikely (< 5%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Moderate", [Risk Probability of Occurence]@row="Very unlikely (< 5%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Moderate", [Risk Probability of Occurence]@row="Not to be ruled out (5% - 25%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Moderate", [Risk Probability of Occurence]@row="Possible (26% - 50%)", "Important", IF(AND([Risk Impact Severity]@row= "Moderate", [Risk Probability of Occurence]@row="Probable (51% - 75%)", "Critical", IF(AND([Risk Impact Severity]@row= "Moderate", [Risk Probability of Occurence]@row="Very Likely (> 75%)", "Critical", IF(AND([Risk Impact Severity]@row= "Minor", [Risk Probability of Occurence]@row="Very Likely (> 75%)", "Important", IF(AND([Risk Impact Severity]@row= "Minor", [Risk Probability of Occurence]@row="Probable (51% - 75%)", "Important", IF(AND([Risk Impact Severity]@row= "Minor", [Risk Probability of Occurence]@row="Possible (26% - 50%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Minor", [Risk Probability of Occurence]@row="Not to be ruled out (5% - 25%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Minor", [Risk Probability of Occurence]@row="Very unlikely (< 5%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Trivial", [Risk Probability of Occurence]@row="Very unlikely (< 5%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Trivial", [Risk Probability of Occurence]@row="Not to be ruled out (5% - 25%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Trivial", [Risk Probability of Occurence]@row="Possible (26% - 50%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Trivial", [Risk Probability of Occurence]@row="Probable (51% - 75%)", "Acceptable", IF(AND([Risk Impact Severity]@row= "Trivial", [Risk Probability of Occurence]@row="Very Likely (> 75%)", "Acceptable"


Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!