IF formula with multiple conditions

Hi! I am trying to create a risk register template and would like to have the columns: 'Impact', 'Likelihood', and 'Risk Score'. The Impact and Likelihood columns will be user selected input from a drop down menu, where the options are 'Low', 'Medium', and 'High'. I would like the 'Risk Score' column to then populate based on the input in the Impact and Likelihood columns. This then becomes a matrix with 9 potential outcomes. Examples: (where Impact x Likelihood = Score), Low x Low = Low; Low x Medium = Low; Low x High = Medium; Medium x Low = Medium; Medium x Medium = Medium; and so on.

I've made it as far as writing an IF statement that gives me 'Low x Low = Low' [=IF(AND(Impact@row = "Low", Likelihood@row = "Low"), "Low")]. I've not been successful at adding any additional outputs to the equation.

Please let me know where I am going wrong! Or let me know if there is a better way to do this than using an IF equation.

Thanks so much, Karen

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 05/24/21 Answer ✓

    Hi,

    Try:

    =IF(AND(Impact@row = "Low", Likelihood@row = "high"), "medium", IF(AND(Impact@row = "high", Likelihood@row = "Low"), "medium", IF(OR(Impact@row = "High", Likelihood@row = "High"), "High", IF(OR(Impact@row = "Medium", Likelihood@row = "Medium"), "Medium", "Low"))))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 05/24/21 Answer ✓

    Hi,

    Try:

    =IF(AND(Impact@row = "Low", Likelihood@row = "high"), "medium", IF(AND(Impact@row = "high", Likelihood@row = "Low"), "medium", IF(OR(Impact@row = "High", Likelihood@row = "High"), "High", IF(OR(Impact@row = "Medium", Likelihood@row = "Medium"), "Medium", "Low"))))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thank you Mark! It worked, but I had to change the OR statements to AND statements. I was able to build out the additional scenarios. I think part of my original problem was not getting the right number of end brackets at the end of the equation. Thanks so much for your help!!

    Karen

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Excellent. Glad you found a solution. Please accept an answer to close it the discussion. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Shubha
    Shubha ✭✭

    Hy @Mark Cronk


    Could you please solve one of my problems? Here is a snapshot of what I'm doing.

    =IF(AND([Supplier Tender Date]@row > [Shipment Date (Actual) (To Warehouse)]@row, [Shipment Date (Actual) (To Site)]@row, "Shipment On Time", IF([Supplier Tender Date]@row < [Shipment Date (Actual) (To Warehouse)]@row, [Shipment Date (Actual) (To Site)]@row, "Shipment Late")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!