IF(AND Nested Formulas

Christal

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

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.

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! 🙂