Using IF AND OR Formulas

Mohammed Sulaiman

I'm trying to populate a column "Risk Impact", based on values from the column : Severity and Likelihood.

The values are as follows:

Value should be Medium

When :

Very Unlikely vs Minor

Unlikely vs Significant

Possible vs Significant

Major vs Likely

Major vs Very Likely

Severe vs Very Likely

Value should be Very Low

When :

Negligible vs Unlikely

Negligible vs Possible

Negligible vs Likely

Negligible vs Almost Certain

Minor vs Almost Certain

Value should be Low

When :

Negligible vs Rare

Minor vs Unlikely

Minor vs Possible

Minor vs Likely

Significant vs Likely

Significant vs Almost Certain

Value should be High

When :

Significant vs Rare

Major vs Unlikely

Major vs Possible

Severe vs Possible

Severe vs Likely

Value should be Extreme

When :

Major vs Rare

Severe vs Rare

Severe vs Unlikely 

I believe a combination of IF, AND, OR is required

markkrebs

=IF(OR(AND(Severity@row = "severe", Likelihood@row = "Likely"), (AND(Severity@row = "severe", Likelihood@row = "Possible"))), "High", IF(OR(AND(Severity@row = "Significant", Likelihood@row = "Likely"), (AND(Severity@row = "Significant", Likelihood@row = "Almost Certain"))), "Low", "nothing"))

this should get you started, you just have to repeat w/ copy/paste the same pattern for all the or and ANDs. this was my result of my small test....

Severe Likely High

Severe Possible High

Significant Almost Certain Low

markkrebs

=IF(OR(AND(Severity@row = "severe", Likelihood@row = "Likely"), (AND(Severity@row = "severe", Likelihood@row = "Possible"))), "High", IF(OR(AND(Severity@row = "Significant", Likelihood@row = "Likely"), (AND(Severity@row = "Significant", Likelihood@row = "Almost Certain"))), "Low", "nothing"))

this should get you started, you just have to repeat w/ copy/paste the same pattern for all the or and ANDs. this was my result of my small test....

Severe Likely High

Severe Possible High

Significant Almost Certain Low

Mohammed Sulaiman

That worked absolutely fine. Thanks,