• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Multiple IFs

Hi

Please can you assist with a smart if
i will have 3 columns ' impact' and 'likelihood' - and based on those number - I will have a derived risk.

I have no clue how to make it - please help


if impact is 1 and likelihood is 1 or 2 or 3 then 'minor' (risk)

if impact is 1 and likelihood is 4 then 'moderate' (risk)

if impact is 1 and likelihood is 5 then 'high' (risk)


if impact is 2 and likelihood is 1 or 2 then 'minor' (risk)

if impact is 2 and likelihood is 3 then 'moderate' (risk)

if impact is 2 and likelihood is 4 or 5 then 'high' (risk)


if impact is 3 and likelihood is 1 then 'minor' (risk)

if impact is 3 and likelihood is 2 then 'moderate' (risk)

if impact is 3 and likelihood is 3 or 4 then 'high' (risk)

if impact is 3 and likelihood is 5 then 'very high' (risk)


if impact is 4 and likelihood is 1 then 'moderate' (risk)

if impact is 4 and likelihood is 2 or 3 then 'high' (risk)

if impact is 4 and likelihood is 4 or 5 then 'very high'(risk)


if impact is 5 and likelihood is 1 or 2 or 3 then 'high' (risk)

if impact is 5 and likelihood is 4 or 5 then 'very high' (risk)


Thank you
David.
 

Attachments

  • How do I make an IF.xlsx
    9 KB · Views: 7
Hi

Please can you assist with a smart if
i will have 3 columns ' impact' and 'likelihood' - and based on those number - I will have a derived risk.

I have no clue how to make it - please help


if impact is 1 and likelihood is 1 or 2 or 3 then 'minor' (risk)

if impact is 1 and likelihood is 4 then 'moderate' (risk)

if impact is 1 and likelihood is 5 then 'high' (risk)


if impact is 2 and likelihood is 1 or 2 then 'minor' (risk)

if impact is 2 and likelihood is 3 then 'moderate' (risk)

if impact is 2 and likelihood is 4 or 5 then 'high' (risk)


if impact is 3 and likelihood is 1 then 'minor' (risk)

if impact is 3 and likelihood is 2 then 'moderate' (risk)

if impact is 3 and likelihood is 3 or 4 then 'high' (risk)

if impact is 3 and likelihood is 5 then 'very high' (risk)


if impact is 4 and likelihood is 1 then 'moderate' (risk)

if impact is 4 and likelihood is 2 or 3 then 'high' (risk)

if impact is 4 and likelihood is 4 or 5 then 'very high'(risk)


if impact is 5 and likelihood is 1 or 2 or 3 then 'high' (risk)

if impact is 5 and likelihood is 4 or 5 then 'very high' (risk)


Thank you
David.


Is this ok?
 

Attachments

  • How do I make an IF.xlsx
    12.8 KB · Views: 8
A2*4+B2*5 is just math, for example with A2=2 and B2=3:
2*4+3*5 = 8+15 = 23

LOOKUP will find the number equal or below it (21) and give the result as "Moderate", as both are the 2nd value in the arrays.
 

Attachments

  • How do I make an IF (2).xlsx
    12.5 KB · Views: 4
There is one point of discrepancy where the risk table you gave has an irregular pattern that I have not captured. i.e. I assess Impact=5, Likelihood=3 to be 'Very high' risk (the same as Impact=4, Likelihood=4).

I have made the defined names 'Impact' and 'Likelihood' to be relative references to the row above anticipating the situation in which multiple risks are brought together as a list.
 

Attachments

  • risk management.xlsx
    15 KB · Views: 7
If David's table is correct, my formula
level: = impact + likelihood - 1
has to become
level: = impact + likelihood - 1 - (impact=5)*(likelihood=3)
to allow for the exception.
Since I didn't define it above, the risk category is a lookup by index
= INDEX( riskCategories, level )
where riskCategories is a named array constant
= {"minor";"minor";"minor";"moderate";"high";"high";"very high";"very high";"very high"}
 
Back
Top