IF formula with multiple conditions

Karen Stanger

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

Mark Cronk

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

Mark Cronk

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

Karen Stanger

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

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

Mark

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")))