DJFANDANGO
Board Regular
- Joined
- Mar 31, 2016
- Messages
- 122
- Office Version
- 365
- Platform
- 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...
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 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Job Step | Severity | Likelihood | Likelihood | ||||||||||
2 | 1 | 4 | 2 | 8 | Med | 1 | 2 | 3 | 4 | 5 | ||||
3 | 2 | 4 | 1 | 4 | Low | Severity | A | B | C | D | E | |||
4 | 3 | 4 | 1 | 4 | Low | 1 | 1 | 2 | 3 | 4 | 5 | |||
5 | 4 | 4 | 3 | 12 | Med | 2 | 2 | 4 | 6 | 8 | 10 | |||
6 | 5 | 4 | 1 | 4 | Low | 3 | 3 | 6 | 9 | 12 | 15 | |||
7 | 6 | 3 | 2 | 6 | Med | 4 | 4 | 8 | 12 | 16 | 20 | |||
8 | 7 | 4 | 1 | 4 | Low | 5 | 5 | 12 | 18 | 24 | 30 | |||
9 | 8 | 1 | 2 | 2 | Low | |||||||||
10 | 9 | Total Tasks | Type | Category | Percent % | |||||||||
11 | 10 | 8 | 5 | Low | 63 | |||||||||
12 | 11 | 3 | Med | 38 | ||||||||||
13 | 12 | |||||||||||||
14 | 13 | Overall Risk | Medium Risk | |||||||||||
15 | 14 | |||||||||||||
16 | 15 | |||||||||||||
17 | 16 | |||||||||||||
18 | 17 | |||||||||||||
19 | 18 | |||||||||||||
20 | 19 | |||||||||||||
21 | 20 | |||||||||||||
22 | 21 | |||||||||||||
23 | 22 | |||||||||||||
24 | 23 | |||||||||||||
25 | 24 | |||||||||||||
26 | 25 | |||||||||||||
27 | 26 | |||||||||||||
28 | 27 | |||||||||||||
29 | 28 | |||||||||||||
30 | 29 | |||||||||||||
Overall Risk Calculator |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D30 | D2 | =B2*C2 |
E2:E30 | E2 | =IF(D2>=15,"High",IF(D2>=6,"Med",IF(D2>=1,"Low",))) |
G11 | G11 | =COUNTIF(E2:E10,"*?") |
H11 | H11 | =COUNTIF(E2:E10,"Low") |
H12 | H12 | =COUNTIF(E2:E10,"Med") |
J11 | J11 | =H11*100/G11 |
J12 | J12 | =H12*100/G11 |
H14 | H14 | =IF(J11<=70,"Medium Risk",IF(J12<=29,"Low Risk",)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
H14:L14 | Cell Value | contains "Medium Risk" | text | NO |
H14:L14 | Cell Value | contains "Low Risk" | text | NO |