Help with nested IF AND statements

Trinity Brookes

Hello

I'm new to Smartsheet and looking for help to convert a really long excel formula into Smartsheet. Is there a maximum number of nested statements? I have pasted it into Smartsheet as is, and get the Unparse error.

Essentially, what I want to do is display specific text when two criteria are met. My formula in Excel is:

=IF(AND(A13="Rare",B13="Insignificant"),"Low",IF(AND(A13="Rare",B13="Minor"),"Low",IF(AND(A13="Rare",B13="Moderate"),"Low",IF(AND(A13="Rare",B13="Major"),"Moderate",IF(AND(A13="Rare",B13="Catastrophic"),"Moderate",IF(AND(A13="Unlikely",B13="Insignificant"),"Low",IF(AND(A13="Unlikely",B13="Minor"),"Low",IF(AND(A13="Unlikely",B13="Moderate"),"Moderate",IF(AND(A13="Unlikely",B13="Major"),"Moderate",IF(AND(A13="Unlikely",B13="High"),"Low",IF(AND(A13="Possible",B13="Insignificant"),"Low",IF(AND(A13="Possible",B13="Minor"),"Moderate",IF(AND(A13="Possible",B13="Moderate"),"Moderate",IF(AND(A13="Possible",B13="Major"),"High",IF(AND(A13="Possible",B13="Catastrophic"),"High",IF(AND(A13="Likely",B13="Insignificant"),"Moderate",IF(AND(A13="Likely",B13="Minor"),"Moderate",IF(AND(A13="Likely",B13="Moderate"),"High",IF(AND(A13="Likely",B13="Major"),"High",IF(AND(A13="Likely",B13="Catastrophic"),"Extreme",IF(AND(A13="Almost Certain",B13="Insignificant"),"Moderate",IF(AND(A13="Almost Certain",B13="Minor"),"High",IF(AND(A13="Almost Certain",B13="Moderate"),"High",IF(AND(A13="Almost Certain",B13="Major"),"Extreme",IF(AND(A13="Almost Certain",B13="Catastrophic"),"Extreme","Not right")))))))))))))))))))))))))

May I please have some help to use this in Smartsheet?

Thankyou

Andrée Starå

Hi @Trinity Brookes

There isn't a limit for nested formulas, but there is a character limit of 4000 characters.

Try something like this. (I've replaced the row number with @row instead)

=IF(AND(A@row = "Rare", B@row = "Insignificant"), "Low", IF(AND(A@row = "Rare", B@row = "Minor"), "Low", IF(AND(A@row = "Rare", B@row = "Moderate"), "Low", IF(AND(A@row = "Rare", B@row = "Major"), "Moderate", IF(AND(A@row = "Rare", B@row = "Catastrophic"), "Moderate", IF(AND(A@row = "Unlikely", B@row = "Insignificant"), "Low", IF(AND(A@row = "Unlikely", B@row = "Minor"), "Low", IF(AND(A@row = "Unlikely", B@row = "Moderate"), "Moderate", IF(AND(A@row = "Unlikely", B@row = "Major"), "Moderate", IF(AND(A@row = "Unlikely", B@row = "High"), "Low", IF(AND(A@row = "Possible", B@row = "Insignificant"), "Low", IF(AND(A@row = "Possible", B@row = "Minor"), "Moderate", IF(AND(A@row = "Possible", B@row = "Moderate"), "Moderate", IF(AND(A@row = "Possible", B@row = "Major"), "High", IF(AND(A@row = "Possible", B@row = "Catastrophic"), "High", IF(AND(A@row = "Likely", B@row = "Insignificant"), "Moderate", IF(AND(A@row = "Likely", B@row = "Minor"), "Moderate", IF(AND(A@row = "Likely", B@row = "Moderate"), "High", IF(AND(A@row = "Likely", B@row = "Major"), "High", IF(AND(A@row = "Likely", B@row = "Catastrophic"), "Extreme", IF(AND(A@row = "Almost Certain", B@row = "Insignificant"), "Moderate", IF(AND(A@row = "Almost Certain", B@row = "Minor"), "High", IF(AND(A@row = "Almost Certain", B@row = "Moderate"), "High", IF(AND(A@row = "Almost Certain", B@row = "Major"), "Extreme", IF(AND(A@row = "Almost Certain", B@row = "Catastrophic"), "Extreme", "Not right")))))))))))))))))))))))))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Trinity Brookes

There isn't a limit for nested formulas, but there is a character limit of 4000 characters.

Try something like this. (I've replaced the row number with @row instead)

=IF(AND(A@row = "Rare", B@row = "Insignificant"), "Low", IF(AND(A@row = "Rare", B@row = "Minor"), "Low", IF(AND(A@row = "Rare", B@row = "Moderate"), "Low", IF(AND(A@row = "Rare", B@row = "Major"), "Moderate", IF(AND(A@row = "Rare", B@row = "Catastrophic"), "Moderate", IF(AND(A@row = "Unlikely", B@row = "Insignificant"), "Low", IF(AND(A@row = "Unlikely", B@row = "Minor"), "Low", IF(AND(A@row = "Unlikely", B@row = "Moderate"), "Moderate", IF(AND(A@row = "Unlikely", B@row = "Major"), "Moderate", IF(AND(A@row = "Unlikely", B@row = "High"), "Low", IF(AND(A@row = "Possible", B@row = "Insignificant"), "Low", IF(AND(A@row = "Possible", B@row = "Minor"), "Moderate", IF(AND(A@row = "Possible", B@row = "Moderate"), "Moderate", IF(AND(A@row = "Possible", B@row = "Major"), "High", IF(AND(A@row = "Possible", B@row = "Catastrophic"), "High", IF(AND(A@row = "Likely", B@row = "Insignificant"), "Moderate", IF(AND(A@row = "Likely", B@row = "Minor"), "Moderate", IF(AND(A@row = "Likely", B@row = "Moderate"), "High", IF(AND(A@row = "Likely", B@row = "Major"), "High", IF(AND(A@row = "Likely", B@row = "Catastrophic"), "Extreme", IF(AND(A@row = "Almost Certain", B@row = "Insignificant"), "Moderate", IF(AND(A@row = "Almost Certain", B@row = "Minor"), "High", IF(AND(A@row = "Almost Certain", B@row = "Moderate"), "High", IF(AND(A@row = "Almost Certain", B@row = "Major"), "Extreme", IF(AND(A@row = "Almost Certain", B@row = "Catastrophic"), "Extreme", "Not right")))))))))))))))))))))))))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Trinity Brookes

Thankyou so much for your help.

Andrée Starå

@Trinity Brookes

You're more than welcome!