< Go Back

Learn from a Risk Matrix Example in Excel

Analyzing risks is often best done visually when there is a great deal of data to consider. Excel allows the user to create a risk matrix that is a visual assessment of risk based on the data provided.  Here is an example of creating a risk matrix in Excel. 

Risk Matrix Example in Excel

There he was, JEFF, a construction worker with LIMO LLC. A scaffold that wasn’t properly fitted had caused JEFF to become bedridden. The management ordered an immediate RISK ASSESSMENT which involves the use of a RISK MATRIX like the one in the example below. In this Matrix, the Y-axis tells us the SEVERITY of the risk involved and the X-axis tells us the LIKELIHOOD of occurrence of the RISK. Hence, the RISK of a hazard is calculated as: A Risk Matrix Example

Risk = likelihood of occurrence (probability) * Severity of harm

Formula for a risk matrix

A RISK MATRIX can be assessed by a formula based on the INDEX and MATCH functions. In the Risk Matrix Example below, the GENERAL FORMULA in Cell D13 is given as:

=INDEX(C5:G9,MATCH(Severity,B5:B9,0),MATCH(Likelihood,B5:B9,0))

The Syntax for this formula is given as: =INDEX(array,row_mumber,(column_number))

Explanation of formula

To get the result based on the RISK MATRIX for RARE * MODERATE as seen in the RISK MATRIX EXAMPLE below, the formula is:

=INDEX(C5:G9,MATCH(D12,B5:B9,0),MATCH(D11,C4:G4,0))

To get the Column value for SEVERITY, the string below is used for the RISK MATRIX EXAMPLE

=MATCH(D12,B5:B9,0)

To get the Row value for LIKELIHOOD, the string below is used for the RISK MATRIX EXAMPLE

=MATCH(D11,C4:G4,0)

The INDEX function returns the number or value that is present in ROW 3 and COLUMN 2 based on the example below and this string: =INDEX(C5:G9,E11,E12)

The method used in the EXAMPLE is preferred to the ALTERNATIVE METHOD because you can vary the SEVERITY and LIKELIHOOD in Cells D11 and D12 respectively to AUTOMATICALLY get your result.

Example

Step 1

  • Generate the RISK MATRIX table in the form below

 

Figure 1- Risk Matrix Example

Step 2

  • Click on Cell E11
  • Type in this string: =MATCH(D11,C4:G4,0)
  • Click on Enter
  • Likewise, click on Cell E12
  • Type in this string: =MATCH(D12,B5:B9,0)
  • Click on Enter
  • Your result must be similar to figure 2

Figure 2- Risk Matrix Example

Step 3

  • Click on Cell D13
  • Type in this string: =INDEX(C5:G9,E11,E12)
  • Click on Enter
  • Your result must be similar to figure 3

Figure 3- Risk Matrix Example

Alternative method

  • Click on Cell D13
  • Type the string below into the Cell

=INDEX(C5:G9,MATCH(D12,B5:B9,0),MATCH(D11,C4:G4,0))

  • Click Enter
  • Your result must be similar to figure 4

Figure 4- Risk Matrix Example

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar