I need help with an IF(AND..IF(AND.. formula that I'm getting an #UNPARSEABLE error on.

I'm trying to replicate a risk rating matrix (image included) by taking the data in 2 cells (Risk Likelihood & Risk Impact) to auto-populate a third cell (Risk Rating). Below is the formula that I am using.

=IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Insignificant"), " Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Insignificant"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Minor"), " Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Minor"), "Low", IF(AND(([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Minor"), "Moderate", IF(AND(([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Moderate"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Major"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Major"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Major"), "Extreme", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Catastrophic"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Catastrophic"), "Extreme", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Catastrophic"), "Extreme"))))))))))))))))))))))))))

Any ideas where I'm going wrong?

Or if there is a shorter, easier way to do this, I would welcome that too!

Find more posts tagged with

Formulas

Accepted answers

Caroline Elliott

Works perfectly now. Thank you Dan, greatly appreciated 🤩

All comments

Dan W

You had a few extra ( in two spots.

Here is the fixed version. Hope this helps.

=IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Insignificant"), " Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Insignificant"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Minor"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Minor"), "Low", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Moderate"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Major"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Major"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Major"), "Extreme", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Catastrophic"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Catastrophic"), "Extreme", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Catastrophic"), "Extreme")))))))))))))))))))))))))

Caroline Elliott

Works perfectly now. Thank you Dan, greatly appreciated 🤩

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.