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]

How about

=IF(AND(B2=5,C2=1),"Med",IF(D2>=15,"High",IF(D2>=6,"Med",IF(D2>=1,"Low",))))

 

[DJFANDANGO]

How about

=IF(AND(B2=5,C2=1),"Med",IF(D2>=15,"High",IF(D2>=6,"Med",IF(D2>=1,"Low",))))

Fluff,

As usual... PERFECT!!! thank you

 

[Fluff]

Glad to help & thanks for the feedback.

 

[DJFANDANGO]

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
	A	B	C			F	G	H	I	J	K	L
1	Job Step	Severity	Likelihood					Likelihood
2	1							1	2	3	4	5
3	2						Severity	A	B	C	D	E
4	3						1	1	2	3	4	5
5	4						2	2	4	6	8	10
6	5						3	3	6	9	12	15
7	6						4	4	8	12	16	20
8	7						5	5	10	15	20	25
9	8
10	9						Total Tasks	Type	Category	Percent %
11	10						0		Low
12	11								Med
13	12								High
14	13						Overall Risk
Overall Risk Calculator

Cell Formulas
Range	Formula
G11	G11	=COUNTIF(E2:E31,"*?")
H11	H11	=COUNTIF(E2:E31,"Low")
H12	H12	=COUNTIF(E2:E31,"Med")
H13	H13	=COUNTIF(E2:E31,"High")
J11	J11	=IFERROR(H11*100/G11,"")
J12	J12	=IFERROR(H12*100/G11,"")
J13	J13	=IFERROR(H13*100/G11,"")
H14	H14	=IF(H13>=1,"High Risk",IF(J11<=70,"Medium Risk",IF(J12<=30,"Low Risk",)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H14:L14	Cell Value	contains "High Risk"	text	NO
H14:L14	Cell Value	contains "Medium Risk"	text	NO
H14:L14	Cell Value	contains "Low Risk"	text	NO

 

[Fluff]

Are you happy to change H4:L8 to words instead of numbers?

 

[DJFANDANGO]

Are you happy to change H4:L8 to words instead of numbers?

Do you mean, "Low, Med, High"?

 

[DJFANDANGO]

I

Do you mean, "Low, Med, High"?

If so, yes, that works for me

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Job Step	Severity						Likelihood
2	1	4B			Med			1	2	3	4	5
3	2	1e			Low		Severity	A	B	C	D	E
4	3	5D			High		1	Low	Low	Low	Low	Low
5	4	1a			Low		2	Low	Low	Low	Med	Med
6	5						3	Low	Low	Med	Med	High
7	6						4	Low	Med	Med	High	High
8	7						5	Med	Med	High	High	High
9	8
10	9						Total Tasks	Type	Category	Percent %
11	10						4	2	Low	50
12	11							1	Med	25
13	12							1	High	25
14	13						Overall Risk	High Risk
15
Main

Cell Formulas
Range	Formula
E2:E8	E2	=IF(B2="","",INDEX($H$4:$L$8,MATCH(LEFT(B2)*1,$G$4:$G$8,0),MATCH(RIGHT(B2),$H$3:$L$3,0)))
G11	G11	=COUNTIF(E2:E31,"*?")
H11	H11	=COUNTIF(E2:E31,"Low")
H12	H12	=COUNTIF(E2:E31,"Med")
H13	H13	=COUNTIF(E2:E31,"High")
J11	J11	=IFERROR(H11*100/G11,"")
J12	J12	=IFERROR(H12*100/G11,"")
J13	J13	=IFERROR(H13*100/G11,"")
H14	H14	=IF(H13>=1,"High Risk",IF(J11<=70,"Medium Risk",IF(J12<=30,"Low Risk",)))

 

[DJFANDANGO]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Job Step	Severity						Likelihood
2	1	4B			Med			1	2	3	4	5
3	2	1e			Low		Severity	A	B	C	D	E
4	3	5D			High		1	Low	Low	Low	Low	Low
5	4	1a			Low		2	Low	Low	Low	Med	Med
6	5						3	Low	Low	Med	Med	High
7	6						4	Low	Med	Med	High	High
8	7						5	Med	Med	High	High	High
9	8
10	9						Total Tasks	Type	Category	Percent %
11	10						4	2	Low	50
12	11							1	Med	25
13	12							1	High	25
14	13						Overall Risk	High Risk
15
Main

Cell Formulas
Range	Formula
E2:E8	E2	=IF(B2="","",INDEX($H$4:$L$8,MATCH(LEFT(B2)*1,$G$4:$G$8,0),MATCH(RIGHT(B2),$H$3:$L$3,0)))
G11	G11	=COUNTIF(E2:E31,"*?")
H11	H11	=COUNTIF(E2:E31,"Low")
H12	H12	=COUNTIF(E2:E31,"Med")
H13	H13	=COUNTIF(E2:E31,"High")
J11	J11	=IFERROR(H11*100/G11,"")
J12	J12	=IFERROR(H12*100/G11,"")
J13	J13	=IFERROR(H13*100/G11,"")
H14	H14	=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!