# Risk Rating Formula

Options
edited 07/26/24

Hi all - I am trying to create a long formula and need some assistance.

For a Risk tracker I have 2 columns (Probability and Impact) both which have list values of High, Medium, Low. Based on those selections I want to have a 3rd column which is the output of the formula, Rating, designated High, Medium, Low. Below are the Rating outputs I want but cannot seem to get the formula right. Can anyone assist or provide some guidance?

Probability

Impact

Rating

High

High

High

High

Medium

High

High

Low

Low

Medium

High

High

Medium

Medium

Medium

Medium

Low

Low

Low

High

Medium

Low

Medium

Low

Low

Low

Low

Tags:

• ✭✭✭✭✭✭
Options

Try:

=IF(AND(Probability@row = "High", OR(Impact@row = "High", Impact@row = "Medium")), "High", IF(AND(Probability@row = "High", Impact@row = "Low"), "Low", IF(AND(Probability@row = "Medium", Impact@row = "High"), "HIgh", IF(AND(Probability@row = "Medium", Impact@row = "Medium"), "Medium", IF(AND(Probability@row = "Medium", Impact@row = "Low"), "Low", IF(AND(Probability@row = "Low", Impact@row = "High"), "Medium", "Low"))))))

• ✭✭✭✭✭✭
Options

In that case, the most efficient "same sheet" formula I can think of would be:

=IF(Probability@row = "High", IF(Impact@row = "Low", "Low", "High"), IF(Probability@row = "Low", IF(Impact@row = "High", "High", "Low"), Impact@row))

• ✭✭✭✭✭✭
Options

I would suggest manually creating a table that looks identical to the one you currently have but on a separate sheet, then referencing this table in a formula with cross sheet references:

=IFERROR(INDEX(COLLECT({Reference Table Rating Column}, {Reference Table Probability Column}, @cell = Probability@row, {Reference Table Impact Column}, @cell = Impact@row), 1), "")

• Options

Thank you for your solution, Paul. I would prefer to create a direct formula in the sheet directly and not have an additional sheet and a cross reference formula.

• ✭✭✭✭✭✭
Options

Try:

=IF(AND(Probability@row = "High", OR(Impact@row = "High", Impact@row = "Medium")), "High", IF(AND(Probability@row = "High", Impact@row = "Low"), "Low", IF(AND(Probability@row = "Medium", Impact@row = "High"), "HIgh", IF(AND(Probability@row = "Medium", Impact@row = "Medium"), "Medium", IF(AND(Probability@row = "Medium", Impact@row = "Low"), "Low", IF(AND(Probability@row = "Low", Impact@row = "High"), "Medium", "Low"))))))

• Options

Thanks so much Nic. Works perfectly!

• ✭✭✭✭✭✭