1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by david gabra, Oct 10, 2018.

  1. david gabra

    david gabra Member

    Messages:
    121
    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.

    Attached Files:

  2. sureshsonti

    sureshsonti Member

    Messages:
    76

    Is this ok?

    Attached Files:

  3. Haz

    Haz Active Member

    Messages:
    114
    In a single formula:
    =LOOKUP(A2*4+B2*5,{9,21,25,36},{"Minor","Moderate","High","Very High"})
    Thomas Kuriakose and sureshsonti like this.
  4. sureshsonti

    sureshsonti Member

    Messages:
    76

    Awesome Haz. What does the highlighted one mean

    LOOKUP(A2*4+B2*5,{9,21,25,36},{"Minor","Moderate","High","Very High"})
  5. Haz

    Haz Active Member

    Messages:
    114
    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.

    Attached Files:

    Thomas Kuriakose and sureshsonti like this.
  6. sureshsonti

    sureshsonti Member

    Messages:
    76
    Thanks Haz
  7. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    425
    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.

    Attached Files:

    Thomas Kuriakose likes this.
  8. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    425
    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"}

Share This Page