# Risk Assessment Calculator

#### DJFANDANGO

##### Board Regular
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
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
Glad to help & thanks for the feedback.

### Excel Facts

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

Replies
1
Views
69
Replies
6
Views
220
Replies
2
Views
67
Replies
2
Views
80
Replies
6
Views
155

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.

### Which adblocker are you using?

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

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