Simplify 2 array formula to calculate risk score

[djbe17]

Hi all,
if any one fancies a challenge could you look at the attached risk score matrix an see if there is a single cell formular to calculate the result in M6
I currently use 3 helper arrays to develop this, which is fine but would love to bring this to a single array fromular

1. The risk score is captured over 5 subjects - each with likelyhood and impact of between 1-5
2. Each risk is the multiplied - likelihood x impact
3. then COUNT the number of risk scores 1-10
4. then multiple the number of scores x the score raised to log 5
5. then sum the result ( col AO)
6. then reduce to a 1-10 output using Log 5 so output is always between 1 and 10

This is great and works as long as the impact / likelyhood are inputted as whole numbers ( set through validation)
What the ask is that a single formular be set so the impact / likelyhood could be a decimal allowing for slightly more granularity... ( although personally i don't like this )

The attached lays out the steps so hopefully the logic is clear.

Risk Calculation .xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1
2														A					B											C
3		RISK MATRIX												Likelyhood + impact for each eare					Count the number of each output from A											Log 5 of the total risk x number from B											Total Risk	Log 5 to reduce to 1-10 output
4			SAFETY		PERFORMANCE		FINANCE		ASSET MENAGEMENT		SATISFACTION & REPUTAION		Risk Score	SAFETY	PERFORMANCE	FINANCE	ASSET MENAGEMENT	SATISFACTION & REPUTAION	Risk Score											Log 5											TOTAL	Risk Score
5			Likely hood	Impact	Likely hood	Impact	Likely hood	Impact	Likely hood	Impact	Likely hood	Impact							0	1	2	3	4	5	6	7	8	9	10	0	1	2	3	4	5	6	7	8	9	10
6		Current Risk	2	1	1	1	2	3	2	3	3	3	6.21	3	2	5	5	6		0	1	1	0	2	1	0	0	0	0	0	0	25	125	0	###	###	0	0	0	0	22025	6.213307996
7		Revised Score after mitigation	1	1	1	1	1	2	1	3	2	3	5.14	2	2	3	4	5		0	2	1	1	1	0	0	0	0	0	0	0	50	125	625	###	0	0	0	0	0	3925	5.141622157
8
9
Risk calculation Sheet

Cell Formulas
Range	Formula
N4	N4	=C4
O4	O4	=E4
P4	P4	=G4
Q4	Q4	=I4
R4	R4	=K4
M6:M7	M6	=IF(ISERROR(AP6),0,AP6)
N6:N7	N6	=C6+D6
O6:O7	O6	=F6+E6
P6:P7	P6	=G6+H6
Q6:Q7	Q6	=J6+I6
R6:R7	R6	=L6+K6
T6:AC7	T6	=COUNTIF($N6:$R6,T$5)
AD6:AN7	AD6	=S6*5^AD$5
AO6:AO7	AO6	=SUM(AE6:AN6)
AP6:AP7	AP6	=LOG(AO6,5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B7	Expression	=#REF!="P"	text	YES
B6:B7	Expression	=#REF!="R"	text	YES
M6:M7	Other Type	Color scale		NO

Cells with Data Validation
Cell	Allow	Criteria
C8:D8	Whole number	between 1 and 5
C6:L6	Whole number	between 1 and 5
C7:L7	Whole number	between 1 and 5

 

[Cubist]

Try:

=LET(a,BYROW(WRAPROWS(C6:L6,2),SUM),b,GROUPBY(a,a,COUNT,0,0),LOG(SUM(5^INDEX(b,0,1)*INDEX(b,0,2)),5))

 

[djbe17]

Try:

=LET(a,BYROW(WRAPROWS(C6:L6,2),SUM),b,GROUPBY(a,a,COUNT,0,0),LOG(SUM(5^INDEX(b,0,1)*INDEX(b,0,2)),5))

Wow - many thanks works a treat . Now going to spend the rest of the day working out how

Many many thanks.