Risk Assessment Calculator

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
122
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
How about
Excel Formula:
=IF(AND(B2=5,C2=1),"Med",IF(D2>=15,"High",IF(D2>=6,"Med",IF(D2>=1,"Low",))))
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Glad to help & thanks for the feedback.
Hey Fluff,

So... I got some good feedback but most of it was that it was time consuming to fill in 'each row' with the numbers...

A question asked was, can the risk calculator work by putting "4B, 3C etc.." directly into the box (would this require another 'helper column'? (this would go in place of 'Severity' and we would remove 'Likelihood', this would be replaced by one box called 'Residual Risk' would that be possible?
Another thing was the fact that if ONE job is High risk, then it remains high no matter how many Low/Medium steps are in place, I have updated the form to include the HIGH 'rule'.

Risk Assessment Calculator v3.xlsx
ABCFGHIJKL
1Job StepSeverityLikelihoodLikelihood
2112345
32SeverityABCDE
43112345
542246810
6533691215
76448121620
875510152025
98
109Total TasksTypeCategoryPercent %
11100 Low 
1211 Med 
1312 High 
1413Overall Risk 
Overall Risk Calculator
Cell Formulas
RangeFormula
G11G11=COUNTIF(E2:E31,"*?")
H11H11=COUNTIF(E2:E31,"Low")
H12H12=COUNTIF(E2:E31,"Med")
H13H13=COUNTIF(E2:E31,"High")
J11J11=IFERROR(H11*100/G11,"")
J12J12=IFERROR(H12*100/G11,"")
J13J13=IFERROR(H13*100/G11,"")
H14H14=IF(H13>=1,"High Risk",IF(J11<=70,"Medium Risk",IF(J12<=30,"Low Risk",)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H14:L14Cell Valuecontains "High Risk"textNO
H14:L14Cell Valuecontains "Medium Risk"textNO
H14:L14Cell Valuecontains "Low Risk"textNO
 
Upvote 0
Are you happy to change H4:L8 to words instead of numbers?
 
Upvote 0
Ok, how about
+Fluff 1.xlsm
ABCDEFGHIJKL
1Job StepSeverityLikelihood
214BMed12345
321eLowSeverityABCDE
435DHigh1LowLowLowLowLow
541aLow2LowLowLowMedMed
65 3LowLowMedMedHigh
76 4LowMedMedHighHigh
87 5MedMedHighHighHigh
98
109Total TasksTypeCategoryPercent %
111042Low50
12111Med25
13121High25
1413Overall RiskHigh Risk
15
Main
Cell Formulas
RangeFormula
E2:E8E2=IF(B2="","",INDEX($H$4:$L$8,MATCH(LEFT(B2)*1,$G$4:$G$8,0),MATCH(RIGHT(B2),$H$3:$L$3,0)))
G11G11=COUNTIF(E2:E31,"*?")
H11H11=COUNTIF(E2:E31,"Low")
H12H12=COUNTIF(E2:E31,"Med")
H13H13=COUNTIF(E2:E31,"High")
J11J11=IFERROR(H11*100/G11,"")
J12J12=IFERROR(H12*100/G11,"")
J13J13=IFERROR(H13*100/G11,"")
H14H14=IF(H13>=1,"High Risk",IF(J11<=70,"Medium Risk",IF(J12<=30,"Low Risk",)))
 
Upvote 0
Solution
Ok, how about
+Fluff 1.xlsm
ABCDEFGHIJKL
1Job StepSeverityLikelihood
214BMed12345
321eLowSeverityABCDE
435DHigh1LowLowLowLowLow
541aLow2LowLowLowMedMed
65 3LowLowMedMedHigh
76 4LowMedMedHighHigh
87 5MedMedHighHighHigh
98
109Total TasksTypeCategoryPercent %
111042Low50
12111Med25
13121High25
1413Overall RiskHigh Risk
15
Main
Cell Formulas
RangeFormula
E2:E8E2=IF(B2="","",INDEX($H$4:$L$8,MATCH(LEFT(B2)*1,$G$4:$G$8,0),MATCH(RIGHT(B2),$H$3:$L$3,0)))
G11G11=COUNTIF(E2:E31,"*?")
H11H11=COUNTIF(E2:E31,"Low")
H12H12=COUNTIF(E2:E31,"Med")
H13H13=COUNTIF(E2:E31,"High")
J11J11=IFERROR(H11*100/G11,"")
J12J12=IFERROR(H12*100/G11,"")
J13J13=IFERROR(H13*100/G11,"")
H14H14=IF(H13>=1,"High Risk",IF(J11<=70,"Medium Risk",IF(J12<=30,"Low Risk",)))
Hey Fluff,

Yup... once again you bring the goods to the table... thank you!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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
Back
Top