Simplify 2 array formula to calculate risk score

djbe17

Board Regular
Joined
Jun 18, 2007
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1
2ABC
3RISK MATRIXLikelyhood + impact for each eareCount the number of each output from ALog 5 of the total risk x number from BTotal RiskLog 5 to reduce to 1-10 output
4SAFETYPERFORMANCEFINANCEASSET MENAGEMENTSATISFACTION & REPUTAIONRisk ScoreSAFETYPERFORMANCEFINANCEASSET MENAGEMENTSATISFACTION & REPUTAIONRisk ScoreLog 5 TOTALRisk Score
5Likely hood ImpactLikely hood ImpactLikely hood ImpactLikely hood ImpactLikely hood Impact012345678910012345678910
6Current Risk21112323336.2132556011021000000251250######0000220256.213307996
7Revised Score after mitigation11111213235.142234502111000000050125625###0000039255.141622157
8
9
Risk calculation Sheet
Cell Formulas
RangeFormula
N4N4=C4
O4O4=E4
P4P4=G4
Q4Q4=I4
R4R4=K4
M6:M7M6=IF(ISERROR(AP6),0,AP6)
N6:N7N6=C6+D6
O6:O7O6=F6+E6
P6:P7P6=G6+H6
Q6:Q7Q6=J6+I6
R6:R7R6=L6+K6
T6:AC7T6=COUNTIF($N6:$R6,T$5)
AD6:AN7AD6=S6*5^AD$5
AO6:AO7AO6=SUM(AE6:AN6)
AP6:AP7AP6=LOG(AO6,5)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B6:B7Expression=#REF!="P"textYES
B6:B7Expression=#REF!="R"textYES
M6:M7Other TypeColor scaleNO
Cells with Data Validation
CellAllowCriteria
C8:D8Whole numberbetween 1 and 5
C6:L6Whole numberbetween 1 and 5
C7:L7Whole numberbetween 1 and 5
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try:
Excel Formula:
=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))
 
Upvote 0
Try:
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top