Risk Matrix/Decision table

Hi folks,

I have a spreadsheet with 6500+ rows of risk assessments using a simple 5x5 matrix. Unfortunately, the policy has changed with respect to defining likelihood and I have to change them all. The tasks have a column representing frequency which I will use to determine the likelihood.

So for example, I have a task name, then a consequence (eg. "5"), then a L/Hood ("e") then the risk rating as determined by the 5x5 matrix, in this case "15".

The likelihood in the example must be changed to "c", which according to the matrix, will change the risk rating to "22".

So I would like to make changes to my L/Hood column and have the risk rating change in accordance with the new consequence/likelihood combination, as defined in my risk matrix.

phew.... did that make sense?

Thanks in advance for any assistance! You guys and girls are always great!

Cheers,

mik

Originally Posted by arlu1201

Do you have a sample file that you can attach?

Thanks for your quick response Arlu. Sorry for my tardy retort!

Attached is "risk matrix example1"

On the data sheet is a number of tasks named in column A. B through J inclusive contain the risk assessment data including consequence and likelihood. The consequence and likelihood need to refer to the matrix to determine risk.

The second sheet, "MATRIX" contains the matrix and reference resultants.

So referring to "DATA" sheet, I need to change Columns E and I (which I will do manually) and would like column F and J to refer to the matrix and lookup the required new result, based on my new input and the value in D and H.

If we look at line 7 on the data sheet, the current values for D through F are: Conseq '5', L/Hood 'e', which according to the matrix, has a risk rating of 15. This task, however, occurs monthly, rather than 3 monthly (as most of the other tasks occur). Accordingly, there is greater likelihood that the hazard scenario can occur. Therefore I need to increase likelihood (Column E) to 'd', which according to the risk matrix means my Risk (Column F) should now equate to '19'.

Please advise if you need any more details. Thanks again.

risk matrix example 1.0.xlsx
you could use the INDEX and MATCH functions to make the spreadsheet look up the Risk numbers automaticaly.

You could replace the Risk numbers with the following code.

=INDEX(MATRIX!$A$1:$F$6, MATCH(DATA!E2,MATRIX!$A$1:$A$6,FALSE), MATCH(DATA!D2,MATRIX!$A$1:$F$1,FALSE))

This will automaticaly check your MATRIX based on the Coneq number and the L/Hood letter.

This way when you go through and manualy update rows D & E or H & I, rows F and J will update based on the data entered.

Also if you have thousands of of the same thing to change, i would advise Highlighting row E or I and using "Find and Replace" (Ctrl + F)

Success! Thanks AlanJ. that's perfect. Exactly what I needed and I actually learnt something as well.