Risk Assessment Calculator

[DJFANDANGO]

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

 

[Fluff]

Glad to help & thanks for the feedback.