Need help with syntax for IF(AND(OR nested statements for a risk assessment table
this is what I have, getting an unparseable error. I changed all the "" to straight quotes. trying to make the cell this formula is in, equal a risk rating as determined by the formula, the graphic matrix is below. HELP!!
=IF((AND(OR([email protected]= "low",[email protected]= "low"),OR([email protected]= "med",[email protected]= "low"), OR([email protected]= "med""[email protected]= "low"),"Low", "1"), IF(AND(OR([email protected]= "low",[email protected]= "high"), OR([email protected]= "med",[email protected]= "med"), OR([email protected]= "high",[email protected]= "low"),"med", "2"), IF(AND(OR([email protected]= "critical",[email protected]= "low"), OR([email protected]= "med",[email protected]= "high",[email protected]= "med",[email protected]= "high"), "high", "3"), IF(AND(OR([email protected]= "critical",[email protected]= "med"), OR([email protected]= "high",[email protected]= "critical"), OR([email protected]= "high",[email protected]= "high"),"critical", "4")
this was my second try:
=IF((AND(OR([email protected]= "low",[email protected]= "low"),OR([email protected]= "med",[email protected]= "low"), OR([email protected]= "med""[email protected]= "low"),"Low", "1"),(OR([email protected]= "low",[email protected]= "high"), OR([email protected]= "med",[email protected]= "med"), OR([email protected]= "high",[email protected]= "low"),"med", "2"), (OR([email protected]= "critical",[email protected]= "low"), OR([email protected]= "med",[email protected]= "high",[email protected]= "med",[email protected]= "high"), "high", "3"), (OR([email protected]= "critical",[email protected]= "med"), OR([email protected]= "high",[email protected]= "critical"), OR([email protected]= "high",[email protected]= "high"),"critical", "4"))
Answers
-
Debbie Sawyer ✭✭✭✭✭✭Hi I've not tested this, just rearranged your second function here, does this work? If not, come back to me and I'll test it out properly!
=IF(OR(AND([email protected]= "low",[email protected]= "low"),AND([email protected]= "med",[email protected]= "low"),AND([email protected]= "med",[email protected]= "low")),"Low",IF(OR(AND([email protected]= "med",[email protected]= "med"),AND([email protected]= "high",[email protected]= "low"),AND([email protected]= "high",[email protected]= "low")),"Medium",IF(OR(AND([email protected]= "low",[email protected]= "critical"),AND([email protected]= "high",[email protected]= "med"),AND([email protected]= "high",[email protected]= "med")),"High","Critical")))
Good luck!
Kind regards Debbie
-
Debbie Sawyer ✭✭✭✭✭✭
Hi - I just tested my formula for you and it appears to be working:
Formula in value is:
=IF(OR(AND([email protected]= "low",[email protected]= "low"), AND([email protected]= "med",[email protected]= "low"), AND([email protected]= "med",[email protected]= "low")), "Low", IF(OR(AND([email protected]= "med",[email protected]= "med"), AND([email protected]= "high",[email protected]= "low"), AND([email protected]= "high",[email protected]= "low")), "Medium", IF(OR(AND([email protected]= "low",[email protected]= "critical"), AND([email protected]= "high",[email protected]= "med"), AND([email protected]= "high",[email protected]= "med")), "High", "Critical")))
-
Thank you! Awesome it works now! i see I was using OR when I should have used AND, and a few other errors as well - thank you so much!
-
Robert Charles ✭✭✭
This is my approach:
Given your table:
Create a logic table of all possible combinations with your risk rating:
Next build a nested if statement exactly like the logic table.
HereG=ImpactandH = Probability.See attached spreadsheet:
=IF(AND(G2=1,H2=1),"LOW",
如果(和(G2 = 1, H2 = 2),“低”,
IF(AND(G2=1,H2=3),"MEDIUM",
IF(AND(G2=2,H2=1),"LOW",
IF(AND(G2=2,H2=2),"MEDIUM",
IF(AND(G2=2,H2=3),"HIGH",
IF(AND(G2=3,H2=1),"MEDIUM",
IF(AND(G2=3,H2=2),"HIGH",
IF(AND(G2=3,H2=3),"CRITICAL",
IF(AND(G2=4,H2=1),"HIGH",
IF(AND(G2=4,H2=2),"CRITICAL",
IF(AND(G2=4,H2=3),"CRITICAL",
"ERROR"))))))))))))
Attached is a spreadsheet:
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/75152/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":39,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
