qualitative risk level calculation

Hi all,

First of all, thank you all for reading this.

I need a formula to calculate the risk level based on impact and likelihood (which are previous columns ) as following:
Untitled.png
if impact=low & likelihood=low then Risk = low etc...

Thank you in advance for your support!

i included a worksheet with the answer.
I recreated the graph you provided and then used an index match, match formula

=INDEX($F$2:$I$5,MATCH($A2,$F$2:$F$5,0),MATCH($B2,$F$2:$I$2,0))

**adapt ranges to fit your spreadsheet.

wow that was fast!
just one question..is there another way to do this? i'm actually trying to implement this in Sharepoint Online where I just have the columns likelihood and impact. Therefore, I'm not sure how to actually adapt your formula in there. Any thoughts?

Thanks so much

i would suggest using the table and hiding it on another tab if possible. Otherwise all i can think of at this moment is to use a lot of nested IF(and()) statements for each answer. I started the formula below, but in all honesty i don't have the time to finish it.

=IF(AND(A2="low",B2="low"),"low",if(AND(A2="low",B2="medium"),"low",if(AND(A2="low",B2="high"),"medium",if(and( etc etc etc etc

its possible there is another way to go about this that isn't as time consuming/tedious.

ok i just thought of something creative. try this if/choose statement.

Formula:

=CHOOSE(IF($A2="low",1,IF($A2="medium",2,IF($A2="high",3,"")))+IF($B2="low",1,IF($B2="medium",2,IF($B2="high",3,""))),,"low","low","medium","high","high")

this worked just fine! thank you so much guys !!

Originally Posted by dosydos

ok i just thought of something creative. try this if/choose statement.

Formula:

=CHOOSE(IF($A2="low",1,IF($A2="medium",2,IF($A2="high",3,"")))+IF($B2="low",1,IF($B2="medium",2,IF($B2="high",3,""))),,"low","low","medium","high","high")

this one worked just fine! thank you so much guys !!

another alternative

Formula:

=LOOKUP(SUM(INDEX(MATCH(LEFT(A2:B2),{"L","M","H"},0),0)),{2,4,5},{"Low","Medium","High"})