IF(AND Nested Formulas

I need to each row to display a 'Score' based on results from:

Likelihood of Risk - Likely, Quite Likely, Unlikely

Impact of Risk - Minor, Visible, Interruption

I am receiving the following message with the below formula - #UNPARSEABLE

Any ideas?

=IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"5",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"3",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"4",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"2",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"4",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"3",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"1","MISSINGVALUE")))))))))

This is the breakdown of the formula.

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"5",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"3",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"4",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"2",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"4",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"3",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"1",

"MISSINGVALUE")))))))))

Find more posts tagged with

Accepted answers

Christal

Thanks! It was the space that was throwing off the formula.

I just decided to take out the extra verbiage and spacing in the column names to simplify.

Thanks again for your help!

All comments

Genevieve P.

Hi @Christal

I've tested your formula and the structure is correct, but it looks like you have the column names written slightly differently.

In your screen capture, it looks like there is a space after the - between Risk- Likely and Risk- Minor, however your formula there isn't a space:

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",

Column names will need to be identical, including spaces, in order for the formula to know what column to look at. Try adding a space after the - and see if this resolves the formula!

Cheers,

Genevieve

Christal

Thanks! It was the space that was throwing off the formula.

I just decided to take out the extra verbiage and spacing in the column names to simplify.

Thanks again for your help!

Genevieve P.

No problem! 🙂

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.