Risk Assessment Calculator

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
102
Office Version
  1. 365
Platform
  1. Windows
Good Day,

I have a simple but complex question...

I have a risk assessment matrix, and based on the job steps it will decide if the 'Overall Risk' is Mediun or Low, my issue is

The matrix is based on a 5 x 5 calculation but:

Severity 1 Likelihood 5 = (5) Low (Green)
Severity 5 Likelihood 1 = (5) Medium (Yellow)

How can I get the formulas I'm using do differentiate from the two?

Any help apprciated...

Risk Assessment Calculator.xlsx
ABCDEFGHIJKL
1Job StepSeverityLikelihoodLikelihood
21428Med12345
32414LowSeverityABCDE
43414Low112345
544312Med2246810
65414Low33691215
76326Med448121620
87414Low5512182430
98122Low
109  Total TasksTypeCategoryPercent %
1110  85Low63
1211  3Med38
1312  
1413  Overall RiskMedium Risk
1514  
1615  
1716  
1817  
1918  
2019  
2120  
2221  
2322  
2423  
2524  
2625  
2726  
2827  
2928  
3029  
Overall Risk Calculator
Cell Formulas
RangeFormula
D2:D30D2=B2*C2
E2:E30E2=IF(D2>=15,"High",IF(D2>=6,"Med",IF(D2>=1,"Low",)))
G11G11=COUNTIF(E2:E10,"*?")
H11H11=COUNTIF(E2:E10,"Low")
H12H12=COUNTIF(E2:E10,"Med")
J11J11=H11*100/G11
J12J12=H12*100/G11
H14H14=IF(J11<=70,"Medium Risk",IF(J12<=29,"Low Risk",))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H14:L14Cell Valuecontains "Medium Risk"textNO
H14:L14Cell Valuecontains "Low Risk"textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,641
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Threads
1,186,719
Messages
5,959,346
Members
438,415
Latest member
Acrid_Stench

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top