Highest severity and overall calc using text

[llphilb]

Hi. I am hoping someone can help me with an excel problem.

I’ve added a screenshot which will hopefully help explain what I’m trying to do.

I would like the 2 boxes in red to auto populate. Seems simple, but I’m sure it’s not.

Impact rating - this should be the highest severity based on the answers manually provided in A4 to E4. For example, if the user enters minor, significant, minor, minor, minor; the impact rating would be ‘significant’.

Overall risk assessment - this should take the impact rating populated above and a frequency which will be manually populated in column G. Put it into a 5 by 5 matrix to give an overall assessment. The frequency options are ‘multiple times a year’, ‘annually’, ‘1 - 5 years, ‘5 - 10 years’ and ‘greater than 10 years’. I've added a link below to an example 5 by 5 matrix - not the exact same as the ratings I have. So the overall assessment would be ‘very low’, ‘low’, ‘moderate’, ‘high’ and ‘critical’.

https://ibb.co/dgC4M6

https://www.project-risk-manager.com/wp-content/uploads/2017/03/5x5-Risk-Matrix.png

Any help offered is much appreciated. Please let me know if there are any questions.

Thanks.

 

[wideboydixon]

Something along these lines I think:

Book1
	A	B	C	D	E	F	G	H
2							Likelihood	Overall Risk Assessment
3	Customer	Financial	Regulatory	Reputational	CASS	Impact Rating
4	Minor	Minor	Minor	Minor	Minor	Minor	Annually	low
Sheet1

Cell Formulas
Range	Formula
H4	=INDEX(Sheet2!$B$1:$F$5,MATCH($F4,Sheet2!$A$1:$A$5,0),MATCH($G4,Sheet2!$B$6:$F$6,0))
F4	{=INDEX({"Minor","Significant","Material","Severe","Extreme"},MAX(MATCH($A4:$E4,{"Minor","Significant","Material","Severe","Extreme"},0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D	E	F
1	Extreme	moderate	moderate	high	critical	critical
2	Severe	low	moderate	high	high	critical
3	Material	low	low	moderate	high	high
4	Significant	very low	low	low	moderate	moderate
5	Minor	very low	very low	low	low	moderate
6		Greater than 10 years	5 - 10 years	1 - 5 years	Annually	Multiple times a year
Sheet2

WBD

 

[llphilb]

Thanks so much - works great!