| Likeli- hood |
Certain | Low | Medium | High | Extreme | Extreme |
| Likely | Low | Medium | Medium | High | Extreme | |
| Possible | Low | Low | Medium | High | High | |
| Unlikely | Low | Low | Low | Medium | High | |
| Rare | Low | Low | Low | Medium | Medium | |
| Insignificant | Minor | Moderate | Major | Severe | ||
| Rating | Consequence | |||||
In SharePoint 2007 this was a challenge but in SharePoint 2010 there was a significantly easier albeit not entirely straight forward way to do this.
The trick here is the askew nature of the matrix and the larger number of combinations. If it was more symetrical then a trivial mathematical formula could have handled this. If there were fewer ratings of likelhood and consequence then a simple logical expression could have done.
SharePoint 2010 Example
So in SharePoint 2010 I created a custom list called Risk Rating that had:
- A choice column called [Likelhood] with the 5 choices "Certain" to "Rare"
- A choice column called [Consequence] with the 5 choices of "Severe" to "Insignificant"
- A calculated column called [Likelihood - Consequence] with a formula = [Likelihood] & " - " & [Consequence]
- A choice column called [Risk Rating] with the 4 choices "Extereme" to "Low"
- A calculated column called [Rating] with the formula = [Risk Rating]
- The [Title] column is changed to optional and hidden
Now in my Risks list, I add a column called [Initial Likelihood - Consequence] which is a lookup column that "links" to the [Likelihood - Consequence] column of the Risk Rating list, and I pull the [Rating] column across as an additional column.
This is an approach that you can use to "link" SharePoint 2010 lists via a composite key. It is not as pretty as I would like but it works.
Note that the resulting columns are called:
- [Initial Likelihood - Consequence]
- [Initial Likelihood - Consequence:Rating]
You can also use this appraoch to have multiple ratings for each risk. For example you may also have a mitigated, reviewed or current risk rating. This simply means having a different column that has the same lookup properties as the first.
Note that the choice columns in the Risk Rating list can not be linked to with a lookup column, nor brought over as additional columns, even though the hidden Title column can be. This is why I had an additional calculated column for Rating, though instead of the convenience of a drop down, I could have just had a single text column for Rating that would have done the trick.
SharePoint 2007 Example
For those of you still on 2007, this approach could be used.
First I added two hidden calculated columns to the Risks list:
Likelihood Rating:
=IF(ISBLANK([Likelihood]),-1,CHOOSE(((FIND([Likelihood],"Rare....,Unlikely,Possible,Likely..,Certain.")-1)/8)+1,3,4,5,7,11))
Consequence Rating:
=IF(ISBLANK([Consequence]),-1,CHOOSE(((FIND([Consequence],"Insignificant,Minor........,Moderate.....,Major........,Severe.......")-1)/13)+1,0,3,4,8,11))
And then I added the final resulting risk as another calculated column:
Risk Rating:
=IF(OR([Likelihood Rating]=-1,[Consequence Rating]=-1),"",CHOOSE(MIN(4,(([Likelihood Rating]*[Consequence Rating])/19)+1),"Low","Medium","High","Extreme"))
Again, not pretty but it works. The real challenge was coming up with the weightings and divisor.
This approach should work equally well in 2010, but the previous option provides easier control.
