Risk Assessment Calculation

[bw5727]

I am trying to calculate the level of risk for a particular item based on the user inputs to two fields.

Column A = Impact; drop down of values
Column B = Probability; drop down of values
Column C = Risk Level; calculated based on the entry in Column A and B and where they intersect in a matrix

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Impact[/TD]
[TD]Probability[/TD]
[TD]Risk Level[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Medium[/TD]
[TD]Unlikely[/TD]
[TD]Low (but calculated by the formula)[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 522"]
<tbody>[TR]
[TD="width: 87"][/TD]
[TD="width: 87"]A[/TD]
[TD="width: 87"]B[/TD]
[TD="width: 87"]C[/TD]
[TD="width: 87"]D[/TD]
[TD="width: 87"]E[/TD]
[TD="width: 87"]F[/TD]
[/TR]
[TR]
[TD="width: 87"]1[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"]Negligible[/TD]
[TD="width: 87"]Low[/TD]
[TD="width: 87"]Medium[/TD]
[TD="width: 87"]High[/TD]
[TD="width: 87"]Severe[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Almost Certain[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl65, bgcolor: #FEB0AD"]High[/TD]
[TD="class: xl65, bgcolor: #FEB0AD"]High[/TD]
[TD="class: xl66, bgcolor: #C00000"]Extreme[/TD]
[TD="class: xl66, bgcolor: #C00000"]Extreme[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Very Likely[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl65, bgcolor: #FEB0AD"]High[/TD]
[TD="class: xl65, bgcolor: #FEB0AD"]High[/TD]
[TD="class: xl66, bgcolor: #C00000"]Extreme[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Likely[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl65, bgcolor: #FEB0AD"]High[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Unlikely[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Rare[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl63, bgcolor: #A9D08E"]Low[/TD]
[TD="class: xl64, bgcolor: #FFE699"]Medium[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1		Negligible	Low	Medium	High	Severe			Medium	Unlikely	Low
2	Almost Certain	Medium	High	High	Extreme	Extreme
3	Very Likely	Medium	Medium	High	High	Extreme
4	Likely	Low	Medium	Medium	Medium	High
5	Unlikely	Low	Low	Low	Medium	Medium
6	Rare	Low	Low	Low	Low	Medium
Today

Cell Formulas
Range	Formula
K1	=INDEX(B2:F6,MATCH(J1,A2:A6,0),MATCH(I1,B1:F1,0))

 

[bw5727]

That worked! THANKS SO MUCH!

 

[Fluff]

You're welcome & thanks for the feedback