1

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
2

Can you add another calculated column that concatenates the 2 Likelihood + Consequence column's number part as a numeric value, i.e.

=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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.