Need help with syntax for IF(AND(OR nested statements for a risk assessment table

Chritine Cianci

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"))

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(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

Debbie Sawyer

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")))

Chritine Cianci

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.

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:

Nested IF 75152 Christiine Cianci.xlsx