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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.