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

Caroline Elliott

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!

Caroline Elliott

Works perfectly now. Thank you Dan, greatly appreciated 🤩

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 🤩