Risk Assessment and Residual Risk Matrix

[dancingdeer]

I am looking for some assistance on making this assessment worksheet function correctly.
I have a column for the Likelihood and a column for the severity I would like to combine the likelihood and the Severity to come together to make the Risk Level ie. Frequent and Hazardious make for a High risk Level.This workbook will be able to be edited by others and the different likelihoods and different Severities will change often so I need the risk level to change with it, also I am looking to change the background cell in the Risk Level to change colors as the level increses or decreases. I have tried many formulas and I seem to only get partially there. Thank You all so much for all your info I have come across many helpers on here.

 

[bbott]

What are all of the possible Likelihoods, Severities, and Risk Levels?
Which combinations determine which Risk Levels?
What color would you like each Risk Level to be?

 

[dancingdeer]

The Possible Likelihoods are Frequent,Probable,Ocassional,Remote and Improbable.
The Possible Severities are Negligible, Marginal, Critical and Catastrophic.
The Risk Levels are Low=Green, Medium=Yellow and High=Red.
The combinations will be a combination of the likelihood and Severity (ie. Frequent Negligible,Frequent Marginal, Frequent Critical, Frequent Catastrophic ect.)
Thank you
Christina

 

[bbott]

Thanks Christina.

Do you have a matrix to determine the risk level (e.g. remote negligible = low, frequent critical = high, etc.)?

 

[dancingdeer]

yes I have a matrix.

 

[bbott]

What is the sheet name and cell range for your matrix?
What is the sheet name and columns for Likelihood and Severity?

 

[dancingdeer]

here I have a copy of worksheets,Thank You!
https://www.dropbox.com/s/rhw009re35hukjc/Risk%20Assesment%20worksheet.xlsx

 

[bbott]

Sorry, I could only view part of your file due to network restrictions on my end, so I will have to make some assumptions.

Assuming your matrix is on Sheet 4 in cells A1:F5 with severity in column A and Likelihood in row 1 ...AND...
Assuming, on your form sheet, Likelihood starting in C5 and Severity starting in D5, put this formula in E5:

=INDEX(Sheet4!$B$2:$F$5,MATCH(D5,Sheet4!$A$2:$A$5,0),MATCH(C5,Sheet4!$B$1:$F$1,0))

Then conditionally format cell E5 with:
=E5="HIGH" <--set fill to red
=E5="MEDIUM" <--set fill to yellow
=E5="LOW" <--set fill to green

Copy formula and formatting down as needed.

Sorry, I know this makes a lot of assumptions, but hopefully it gets you going in the right direction.

 

[dancingdeer]

Thank You Thank You I am very greatful for your help.
Have a blessed week
Christina

 

[bbott]

You're welcome!