Multiply Multiple Results

[Pestomania]

Hi,

I know that I made this much much harder than I needed it to be. I am trying to average all of the Impact scores. How can I do this better?

Critical IT Asset Risk Register.xlsx
	I	J	K	L
1	Impact - Financial	Impact - Operational	Impact - Reputational	Inherent Impact Rating
2	(Moderate) - $20 million - $100 million	(Low) - Noticeable but manageable impact on operations	(Low) - Reputational harm at local level, but recoverable	2*2*3
Sheet2

Cell Formulas
Range	Formula
L2	L2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("*",TRUE,MID([@[Impact - Reputational]],2,FIND(")",[@[Impact - Reputational]])-2),MID([@[Impact - Operational]],2,FIND(")",[@[Impact - Operational]])-2),MID([@[Impact - Financial]],2,FIND(")",[@[Impact - Financial]])-2)),"Very Low",1),"Low",2),"Moderate",3),"Elevated",4),"High",5)

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=Financial_Impact
J2	List	=Operational_Impact
K2	List	=Reputational_Impact

Here is the back end:

Score	Type	Likelihood of Occurrence	Impact - Financial	Impact - Operational	Impact - Reputational
1	Very Low	(Very Low) Almost Impossible - Once every ten years or less frequent: <1%	(Very Low) - <$5 million	(Very Low) - No operational impact or loss of business	(Very Low) - No reputational exposure
2	Low	(Low) Extremely Unlikely - Once every 5 years: 1%-5%	(Low) - $5 million - $20 million	(Low) - Noticeable but manageable impact on operations	(Low) - Reputational harm at local level, but recoverable
3	Moderate	(Moderate) Possible Sometimes - Once per year: 5%-10%	(Moderate) - $20 million - $100 million	(Moderate) - Impact on operations at one site or line of business, requiring careful management	(Moderate) - Reputational harm at regional level, somewhat recoverable
4	Elevated	(Elevated) Isolated Incidents - Once per three months: 10%-20%	(Elevated) - $100 million - $200 million	(Elevated) - Impact on operations at multiple sites or lines of business, requiring careful management	(Elevated) - Reputational harm at national level, somewhat recoverable
5	High	(High) Repeated Incidents - Once per month or more frequent: >20%	(High) - >$200 million	(High) - Impact on operations across entire organization	(High) - Reputational harm at national or international level, sustained{5}

 

[JamesCanale]

The product of all of the impacts then:

MrExcelPlayground23.xlsx
	F	G	H	I	J	K	L
1				Impact - Financial	Impact - Operational	Impact - Reputational	Inherent Impact Rating
2				(Moderate) - $20 million - $100 million	(Low) - Noticeable but manageable impact on operations	(Low) - Reputational harm at local level, but recoverable	12
3
4	(Very Low)
5	(Low)				2	2
6	(Moderate)			3
7	(Elevated)
8	(High)
Sheet23

Cell Formulas
Range	Formula
L2	L2	=PRODUCT(IFERROR(SEARCH(F4:F8,I2:K2)*SEQUENCE(5),""))
I4:K8	I4	=IFERROR(SEARCH(F4:F8,I2:K2)*SEQUENCE(5),"")
Dynamic array formulas.

However, as a risk manager, I would take the impact as the worst individual impact and manage the risks in that way. For example, in this way, something with two moderate impacts would be a bigger deal that one high impact - but I bet that is not practical.