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(Probability@row = "low", Impact@row = "low"),OR(Impact@row = "med", Probability@row = "low"), OR(Probability@row = "med"" Impact@row = "low"),"Low", "1"), IF(AND(OR(Impact@row = "low", Probability@row = "high"), OR(Probability@row = "med", Impact@row = "med"), OR(Impact@row = "high", Probability@row = "low"),"med", "2"), IF(AND(OR(Impact@row = "critical", Probability@row = "low"), OR(Probability@row = "med", Impact@row = "high", Impact@row = "med", Probability@row = "high"), "high", "3"), IF(AND(OR(Impact@row = "critical", Probability@row = "med"), OR(Probability@row = "high", Impact@row = "critical"), OR(Impact@row = "high", Probability@row = "high"),"critical", "4")
this was my second try:
=IF((AND(OR(Probability@row = "low", Impact@row = "low"),OR(Impact@row = "med", Probability@row = "low"), OR(Probability@row = "med"" Impact@row = "low"),"Low", "1"),(OR(Impact@row = "low", Probability@row = "high"), OR(Probability@row = "med", Impact@row = "med"), OR(Impact@row = "high", Probability@row = "low"),"med", "2"), (OR(Impact@row = "critical", Probability@row = "low"), OR(Probability@row = "med", Impact@row = "high", Impact@row = "med", Probability@row = "high"), "high", "3"), (OR(Impact@row = "critical", Probability@row = "med"), OR(Probability@row = "high", Impact@row = "critical"), OR(Impact@row = "high", Probability@row = "high"),"critical", "4"))
Answers

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(Probability@row = "low", Impact@row = "low"),AND(Impact@row = "med", Probability@row = "low"),AND(Probability@row = "med", Impact@row = "low")),"Low",IF(OR(AND(Probability@row = "med", Impact@row = "med"),AND(Impact@row = "high", Probability@row = "low"),AND(Probability@row = "high", Impact@row = "low")),"Medium",IF(OR(AND(Probability@row = "low", Impact@row = "critical"),AND(Impact@row = "high", Probability@row = "med"),AND(Probability@row = "high", Impact@row = "med")),"High","Critical")))
Good luck!
Kind regards Debbie

Hi  I just tested my formula for you and it appears to be working:
Formula in value is:
=IF(OR(AND(Probability@row = "low", Impact@row = "low"), AND(Impact@row = "med", Probability@row = "low"), AND(Probability@row = "med", Impact@row = "low")), "Low", IF(OR(AND(Probability@row = "med", Impact@row = "med"), AND(Impact@row = "high", Probability@row = "low"), AND(Probability@row = "high", Impact@row = "low")), "Medium", IF(OR(AND(Probability@row = "low", Impact@row = "critical"), AND(Impact@row = "high", Probability@row = "med"), AND(Probability@row = "high", Impact@row = "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!

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.
Here G=Impact and H = Probability. See attached spreadsheet:
=IF(AND(G2=1,H2=1),"LOW",
IF(AND(G2=1,H2=2),"LOW",
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
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!