22 Jul Nested IFs and Risks Lists

<Early morning coffee>

When it comes to Risks and Issues, I find that many of my customers wish to transfer their existing Risks and Issues Matrix, often held in Excel , directly into the pre-existing SharePoint lists on the project site.  Excel is an excellent tool, managing risks is complex, and so consequently the spreadsheet can become a behemoth, and as such, becomes both unwieldy and unworkable.  A project server implementation is a great time to review the risk and issue tracking process…

As it exists, the probability and impact fields within the risk form accept numeric values, and the product of these fields is used to determine the exposure field.  This works really well, but one of my customers wanted to take this one stage further and implement the following type of Probability/Impact calculations…

As can be seen, the probability is classed as Very Low, Low, Medium, High or Very High, and each classification has an associated value (0.1, 0.3, 0.5, 0.7, 0,9), and a similar classification, but different values, exists for the impact.  The product of the Probability and Impact is one of 25 unique values between 0.005 and 0.72.

In order to implement such a solution, we need to configure new probability, impact and exposure fields (remember to hide the old ones).  The new probability and impact fields are simple, they are just a drop down list with the values Very Low, Low, Medium, High or Very High as choices.  The new exposure field is a little more complex because it holds the product of the values between 0.0005 and 0.72.  Calculated fields in SharePoint can utilise the if function, so that we can say

If (Probability = Very Low) AND (Impact = Very Low) then the product = 0.0005

If we then take this to the next step, we could use a nested if statement to cycle through all 25 options, eg

If ((test) then “result”

elseif ((test1) then “result1”

elseif ((test2) then “result2” …. through to

elseif ((test24) then “result24” else “result25”))))))))))))))))))))))))

This is standard functionality in many programming languages, and because “nested ifs” can become quite complex, other code structures such as CASE statements can be used instead.   So, you should now be asking the question, “It’s all interesting, but why bother with the lesson Ben?”  The answer is that SharePoint doesn’t have a CASE statement, and only supports 7 nested if statements, and we need 24 to implement our solution…!   A simple solution (but hardly documented) does exist because we can concatenate multiple nested ifs together using the & symbol.

If ((test) then “result”

elseif ((test1) then “result1”

elseif ((test2) then “result2” …. through to

elseif ((test6) then “result6” else “result7”)))))))

&

If ((test8) then “result8”

elseif ((test9) then “result9”

elseif ((test10) then “result10” …. through to

elseif ((test13) then “result13” else “result14”)))))))

Sharepoint has a slightly different syntax than the pseudo code above and the actual code format looks like…

=IF(AND([Probability Selection]=”Very Low”,[Impact Selection]=”Very Low”),”0.005″, IF(AND([Probability Selection]=”Low”,[Impact Selection]=”Very Low”),”0.015″,….

Therefore the whole code solution for the new calculated exposure field is

=IF(AND([Probability Selection]=”Very Low”,[Impact Selection]=”Very Low”),”0.005″,
IF(AND([Probability Selection]=”Low”,[Impact Selection]=”Very Low”),”0.015″,
IF(AND([Probability Selection]=”Medium”,[Impact Selection]=”Very Low”),”0.025″,
IF(AND([Probability Selection]=”High”,[Impact Selection]=”Very Low”),”0.035″,
IF(AND([Probability Selection]=”Very High”,[Impact Selection]=”Very Low”),”0.045″,””)))))&
IF(AND([Probability Selection]=”Very Low”,[Impact Selection]=”Low”),”0.01″,
IF(AND([Probability Selection]=”Low”,[Impact Selection]=”Low”),”0.03″,
IF(AND([Probability Selection]=”Medium”,[Impact Selection]=”Low”),”0.05″,
IF(AND([Probability Selection]=”High”,[Impact Selection]=”Low”),”0.07″,
IF(AND([Probability Selection]=”Very High”,[Impact Selection]=”Low”),”0.09″,””)))))&
IF(AND([Probability Selection]=”Very Low”,[Impact Selection]=”Medium”),”0.02″,
IF(AND([Probability Selection]=”Low”,[Impact Selection]=”Medium”),”0.06″,
IF(AND([Probability Selection]=”Medium”,[Impact Selection]=”Medium”),”0.10″,
IF(AND([Probability Selection]=”High”,[Impact Selection]=”Medium”),”0.14″,
IF(AND([Probability Selection]=”Very High”,[Impact Selection]=”Medium”),”0.18″,””))))&
IF(AND([Probability Selection]=”Very Low”,[Impact Selection]=”High”),”0.04″,
IF(AND([Probability Selection]=”Low”,[Impact Selection]=”High”),”0.12″,
IF(AND([Probability Selection]=”Medium”,[Impact Selection]=”High”),”0.20″,
IF(AND([Probability Selection]=”High”,[Impact Selection]=”High”),”0.28″,
IF(AND([Probability Selection]=”Very High”,[Impact Selection]=”High”),”0.36″,””))))&
IF(AND([Probability Selection]=”Very Low”,[Impact Selection]=”Very High”),”0.08″,
IF(AND([Probability Selection]=”Low”,[Impact Selection]=”Very High”),”0.24″,
IF(AND([Probability Selection]=”Medium”,[Impact Selection]=”Very High”),”0.40″,
IF(AND([Probability Selection]=”High”,[Impact Selection]=”Very High”),”0.56″,
IF(AND([Probability Selection]=”Very High”,[Impact Selection]=”Very High”),”0.72″,””))))

You can take the above code and paste it into SharePoint, but make sure there are no hidden line breaks etc.  In truth you wouldn’t need a new calculated exposure field, you could use the existing one which has the advantage that it is in the reporting database.

If you wanted something slightly less complex, then rather than returning a value, you could just return a Red, Amber Green value, or even a combination of both.  The grid below shows which values should be Red, Amber or Green.

Enjoy,  Ben.