Formula for risk assessment

Another view - It would be clearer if you have/prepare a Risk1 / Risk2 table which covers the results for all possible combinations, eg the table below in say A1:D4   : 

 

Risk1 <br><br>Risk2	Low	Medium	High
Low	1	2	3
Medium	2	3	4
High	3	4	5

 

Then if you have the data for Risk1/2 running in F2:G2 down (these data could be returns from various parties) 

eg below

Risk1	Risk2	RiskScore
Low	Medium	2
Medium	High	4

etc

you could use this in H2:

=IF(COUNTA(F2:G2)<2,"",INDEX($B$2:$D$4,MATCH(F2,$B$1:$D$1,0),MATCH(G2,$A$2:$A$4,0)))

to extract the result from the table in A1:D4

Copy H2 down to return correspondingly for all other rows

I built just how you said and it works to create the table to calculate the data, now how do I put it into practice.  I currently have the data on "Sheet 2", and the data that I want it to calculate is on "Table 1".  Sheet 2 looks like: Which is A1:H8

So when I put this formula: =IF(COUNTA(Sheet2!F2:G2)<2,"",INDEX(Sheet2!$B$2:$D$4,MATCH(Sheet2!F2,Sheet2!$B$1:$D$1,0),MATCH(Sheet2!G2,$A$2:$A$4,0)))

It still returns this below, even though it should be a 2.  So it is just referencing just the first row, I also tried the vlookup but I can't figure out how to do this with lookup up the two cells. I am missing something please help.

Another view - It would be clearer if you have/prepare a Risk1 / Risk2 table which covers the results for all possible combinations, eg the table below in say A1:D4   : 

Then if you have the data for Risk1/2 running in F2:G2 down (these data could be returns from various parties) 

eg below

etc

you could use this in H2:

=IF(COUNTA(F2:G2)<2,"",INDEX($B$2:$D$4,MATCH(F2,$B$1:$D$1,0),MATCH(G2,$A$2:$A$4,0)))

to extract the result from the table in A1:D4

Copy H2 down to return correspondingly for all other rows

First off, to confirm the calculations behind your example:

Low + Low = 1    therefore Low = 0.5?

Low + Medium = 2   therefore Medium = 1.5?

I'll guess that High = 3

Here's one way to do this; use named ranges.

In cells A1:B3, insert the following:

   Low          0.5

   Medium   1.5

   High         3

So 0.5 is in B1, 3 is in B3.

Now create three named ranges in cells B1, B2 and B3, calling them Low, Medium and High respectively.

If cell D1 contains "Low"  and E1 contains "Medium", in cell F1 type  =INDIRECT(D1) + INDIRECT(E1)

This should return 2.

Hope that helps.

Cheers

Rich

Hi,

Questions which are defined by saying 'and so on' may be clear to you but they certainly aren't to us (me) so I'll guess.

I created a small table like the one below that assigns a risk value to each of the probability/potential element. I put my table in M1:N3

now with a Probability in a2 and a potential in B2 I used this formula to calculate the risk

=VLOOKUP(A2,$M$1:$N$3,2,FALSE)+VLOOKUP(A2,$M$1:$N$3,2,FALSE)

You can take this one stage further and create a second table which looks at the risk and decides whether the risk is acceptable, requires mitigation etc. The table would be similar to the one below and you could look up the risk using VLOOKUP.

Edit.. I don't know where you are but in the UK where I am the risk assesments and  HSE have become an industry in themselves so I googled for 'Excel risk assesment template' and got a lot of hits.

Look here

http://www.safetyphoto.co.uk/risk_assessment/index.htm