Risk Assessment Matrices are a critical tool in Project Management, enabling teams to evaluate and prioritize risks by considering their likelihood and severity. Here’s a guide on setting them up in monday.com and leveraging formulas to automatically calculate the risk score based on these two dimensions.
If you are not familiar with the Advanced Formula Booster app, designed to reinvent formulas in monday.com, you may want to read The basics of the Advanced Formula Booster. It walks you through the different steps of creating your first formula, then automating it (screenshots + video).
Step 1: Dedicate a Board to the Matrix
Here is an example of Risk Assessment Matrix set up as a monday.com board.
Step 2: Create the Formula
The matrix is in its own board. The risks to evaluate in another one. You may wonder how we are going to connect both. No need to. The Advanced Formula Booster is able to read (and update, but here we only need reading) data from another board. So we can create the formula that will read the matrix in the board that contains the risks, without problem.
Here is the syntax of the formula.
1: –Get Likelyhood Row Number
2: [LikelihoodRow]=IF({Likelihood}=”Very Likely”,1)
3: [LikelihoodRow]=IF({Likelihood}=”Likely”,2)
4: [LikelihoodRow]=IF({Likelihood}=”Possible”,3)
5: [LikelihoodRow]=IF({Likelihood}=”Unlikely”,4)
6: [LikelihoodRow]=IF({Likelihood}=”Very Unlikely”,5)
7: –Get Risk Scores by Item Severity
8: [RiskValues]=IF({Severity}=”Negligible”,{MatrixItems.Negligible})
9: [RiskValues]=IF({Severity}=”Minor”,{MatrixItems.Minor})
10: [RiskValues]=IF({Severity}=”Moderate”,{MatrixItems.Moderate})
11: [RiskValues]=IF({Severity}=”Significant”,{MatrixItems.Significant})
12: [RiskValues]=IF({Severity}=”Severe”,{MatrixItems.Severe})
13: –Get the Correct Risk Score
14: {Risk Assessment}=GETELEMENT([LikelihoodRow],[RiskValues])
Notice there are 2 types of column placeholders. One is using a MatrixItems prefix. The other one uses no prefix.
- The placeholders without prefix refer to columns in the current board.
- The placeholders with prefix refers to columns in the Matrix board. For instance, {MatrixItems.Negligible} returns the values of the Negligible column in the Risk Assessment Matrix (all the values of the columns are returned in one list).
To better understand the formula, below is a screenshot of the simulation we ran on an item which Likelyhood is set to “Very Likely” and Severity to “Moderate”.
In the 1st section of the formula, we get the likelyhood of the item and store the corresponding row number it occupies in the matrix. In this example, the row number is 1 (Very Likely).
In the 2nd section, we get the list of scores for the Item’s Severity. These are retrieved using {MatrixItems.Moderate}. We get a list of scores: Med Hi|Medium|Medium|Low Med|Low Med (the | is the character used by the Advanced Formula Booster to separate elements of a list).
On line 14, we simply extract the correct score in the list of scores, i.e. the one in Row #1, ‘Med Hi’.
Step 3: Add 2 Automations to the Automation Center
We need 2 automations added to your monday.com board. One to run the formula when the ‘Severity’ column changes and one when the ‘Likelihood’ column changes.
Step 4: Test
Advanced Techniques to make this formula shorter and more flexible
Let’s take the formula a step further. What if somehow the rows of the matrix get shuffled? What if you decide to rename one of the likelyhood grades?
In the above formula, instructions on rows 2 to 6 can be replaced by a single instruction that makes no reference to the actual likelyhood grades.
1: –Get Likelyhood Row Number
2: [LikelihoodRow]=FINDPOSITION({Likelihood},{MatrixItems.Name})
3: –Get Risk Scores by Item Severity
4: [RiskValues]=IF({Severity}=”Negligible”,{MatrixItems.Negligible})
5: [RiskValues]=IF({Severity}=”Minor”,{MatrixItems.Minor})
6: [RiskValues]=IF({Severity}=”Moderate”,{MatrixItems.Moderate})
7: [RiskValues]=IF({Severity}=”Significant”,{MatrixItems.Significant})
8: [RiskValues]=IF({Severity}=”Severe”,{MatrixItems.Severe})
9: –Get the Correct Risk Score
10: {Risk Assessment}=GETELEMENT([LikelihoodRow],[RiskValues])
By using the FINDPOSITION function on the names of the items in the Matrix board, we immediately get the row number. Indeed {MatrixItems.Name} returns a list made of the likelihood grades (Very Likely|Likely|Possible|Unlikely|Very Unlikely).
To push it even further, Column Late Binding can be employed to select the appropriate severity column from which to extract the risk values.
1: –Get Likelyhood Row Number
2: [LikelihoodRow]=FINDPOSITION({Likelihood},{MatrixItems.Name})
3: –Get Risk Scores by Item Severity using Late Binding
4: [Severity]={Severity}
5: [RiskValues]={MatrixItems.[Severity]}
6: –Get the Correct Risk Score
7: {Risk Assessment}=GETELEMENT([LikelihoodRow],[RiskValues])
Conclusion
This use case demonstrates how the ability for the Advanced Formula Booster to read (and update) columns in other monday.com boards is a true game-changer, expanding the boundaries of potential applications even further.