Risk and Opportunity Matrix - IF/AND Statements
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?
=如果(((风险影响程度)@row =“关键”,[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
-
Paul Newcome ✭✭✭✭✭✭Looks like you are forgetting to close out your AND function(s).
=IF(AND(.......),"Critical")
Answers
-
sophiaashepard ✭✭✭
Here is my test that I also get the incorrect argument error. I even tried adding a statement at the end for when the conditions are not true and still did not work.
-
Paul Newcome ✭✭✭✭✭✭
Looks like you are forgetting to close out your AND function(s).
=IF(AND(.......),"Critical")
Help Article Resources
Categories
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>
I hope that makes sense.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108864,"type":"question","name":"Is there a formula to show as %","excerpt":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % =\"We are at \" + [% closed rate]@row + \"…","snippet":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a…","categoryID":322,"dateInserted":"2023-08-11T17:22:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":44,"score":null,"hot":3383558496,"url":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","format":"Rich","lastPost":{"discussionID":108864,"commentID":390301,"name":"Re: Is there a formula to show as %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390301#Comment_390301","dateInserted":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T19:29:56+00:00","dateAnswered":"2023-08-11T17:49:58+00:00","acceptedAnswers":[{"commentID":390269,"body":"
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/106886/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":62,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
