# Calculated Column based on two others - Is there a limit on nested IF statements?

I'm trying to set up an automated column based on two others.

Likelihood + Consequence = Calc Column

• 1 - Rare 1 - Minimal = 1 - Low
• 1 - Rare 5 - Severe = 10 - Medium
• 1 - Rare 2 - Minor = 3 - Low
• 1 - Rare 3 - Moderate = 5 - Low
• 1 - Rare 4 - Major = 8 - Low
• 2 - Unlikely 3 - Moderate = 12 - Medium
• 2 - Unlikely 4 - Major = 14 - Medium
• 2 - Unlikely 5 - Severe = 16 - High
• 2 - Unlikely 1 - Minimal = 2 - Low
• 2 - Unlikely 2 - Minor = 6 - Low
• 3 - Possible 2 - Minor = 11 - Medium
• 3 - Possible 3 - Moderate = 17 - High
• 3 - Possible 4 - Major = 19 - High
• 3 - Possible 5 - Severe = 21 - High
• 3 - Possible 1 - Minimal = 4 - Low
• 4 - Likely 2 - Minor = 13 - Medium
• 4 - Likely 3 - Moderate = 18 - High
• 4 - Likely 4 - Major = 22 - Extreme
• 4 - Likely 5 - Severe = 24 - Extreme
• 4 - Likely 1 - Minimal = 7 - Low
• 5 - Almost certain 2 - Minor = 15 - High
• 5 - Almost certain 3 - Moderate = 20 - High
• 5 - Almost certain 4 - Major = 23 - Extreme
• 5 - Almost certain 5 - Severe = 25 - Extreme
• 5 - Almost certain 1 - Minimal = 9 - Medium

I have tested my thinking and statement in Excel and it works fine but as soon as I try it in SharePoint on both Designer and the UI it barfs.

Here is the nested IF statement;

`=IF(AND([Consequence]="1 - Minimal",[Likelihood]="1 - Rare"),"1 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="1 - Rare"),"3 - Low",IF(AND([Consequence]=" 3 - Moderate ",[Likelihood]="1 - Rare"),"5 - Low",IF(AND([Consequence]=" 4 - Major ",[Likelihood]="1 - Rare"),"8 - Low",IF(AND([Consequence]="5 - Severe",[Likelihood]="1 - Rare"),"10 - Medium",IF(AND([Consequence]="1 - Minimal",[Likelihood]="2 - Unlikely"),"2 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="2 - Unlikely"),"6 - Low",IF(AND([Consequence]="3 - Moderate",[Likelihood]="2 - Unlikely"),"12 - Medium",IF(AND([Consequence]="4 - Major",[Likelihood]="2 - Unlikely"),"14 - Medium",IF(AND([Consequence]="5 - Severe",[Likelihood]="2 - Unlikely"),"16 - High",IF(AND([Consequence]="1 - Minimal",[Likelihood]="3 - Possible"),"4 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="3 - Possible"),"11 - Medium",IF(AND([Consequence]="3 - Moderate",[Likelihood]="3 - Possible"),"17 - High",IF(AND([Consequence]="4 - Major",[Likelihood]="3 - Possible"),"19 - High",IF(AND([Consequence]="5 - Severe",[Likelihood]="3 - Possible"),"21 - High",IF(AND([Consequence]="1 - Minimal",[Likelihood]="4 - Likely"),"7 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="4 - Likely"),"13 - Medium",IF(AND([Consequence]="3 - Moderate",[Likelihood]="4 - Likely"),"18 - High",IF(AND([Consequence]="4 - Major",[Likelihood]="4 - Likely"),"22 - Extreme",IF(AND([Consequence]="5 - Severe",[Likelihood]="4 - Likely"),"24 - Extreme",IF(AND([Consequence]="1 - Minimal",[Likelihood]="5 – Almost certain"),"9 - Medium",IF(AND([Consequence]="2 - Minor",[Likelihood]="5 – Almost certain"),"15 - High",IF(AND([Consequence]="3 - Moderate",[Likelihood]="5 – Almost certain"),"20 - High",IF(AND([Consequence]="4 - Major",[Likelihood]="5 – Almost certain"),"23 - Extreme",IF(AND([Consequence]="5 - Severe",[Likelihood]="5 – Almost certain"),"25 - Extreme")))))))))))))))))))))))))`

Here it is working in Excel Excel Doc with Calc working

I have managed to get this one working, 6 nests short of what I need:

`=IF(AND([Consequence]="1 - Minimal",[Likelihood]="1 - Rare"),"1 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="1 - Rare"),"3 - Low",IF(AND([Consequence]="3 - Moderate",[Likelihood]="1 - Rare"),"5 - Low",IF(AND([Consequence]="4 - Major",[Likelihood]="1 - Rare"),"8 - Low",IF(AND([Consequence]="5 - Severe",[Likelihood]="1 - Rare"),"10 - Medium",IF(AND([Consequence]="1 - Minimal",[Likelihood]="2 - Unlikely"),"2 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="2 - Unlikely"),"6 - Low",IF(AND([Consequence]="3 - Moderate",[Likelihood]="2 - Unlikely"),"12 - Medium",IF(AND([Consequence]="4 - Major",[Likelihood]="2 - Unlikely"),"14 - Medium",IF(AND([Consequence]="5 - Severe",[Likelihood]="2 - Unlikely"),"16 - High",IF(AND([Consequence]="1 - Minimal",[Likelihood]="3 - Possible"),"4 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="3 - Possible"),"11 - Medium",IF(AND([Consequence]="3 - Moderate",[Likelihood]="3 - Possible"),"17 - High",IF(AND([Consequence]="4 - Major",[Likelihood]="3 - Possible"),"19 - High",IF(AND([Consequence]="5 - Severe",[Likelihood]="3 - Possible"),"21 - High",IF(AND([Consequence]="1 - Minimal",[Likelihood]="4 - Likely"),"7 - Low",IF(AND([Consequence]="2 - Minor",[Likelihood]="4 - Likely"),"13 - Medium",IF(AND([Consequence]="3 - Moderate",[Likelihood]="4 - Likely"),"18 - High",IF(AND([Consequence]="4 - Major",[Likelihood]="4 - Likely"),"22 - Extreme")))))))))))))))))))`

• Try using several calculated columns to solve this as there are restrictions in place that limit how many statements you can use at once (this is for an older version but it should remain true-ish for later versions as well): social.technet.microsoft.com/Forums/office/en-US/… – Roland Mar 15 '16 at 22:41

``````=VALUE(LEFT(Likelihood,1) + LEFT(Consequence,1))
This would end up with unique numerical values of the concatenated numerical column values, i.e. `= VALUE('11') or VALUE('31') or VALUE('12').`
Then use the `CHOOSE` function in the actual column to show a textual representation of this, `CHOOSE` goes up to 29 items.