Risk Rating Formula

Daniel Miller PMP · 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?

Thanks for your help!

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

Nic Larsen · 07/26/24

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

Paul Newcome · 07/29/24

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

Paul Newcome · 07/26/24

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

Daniel Miller PMP · 07/26/24

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.

Nic Larsen · 07/26/24

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

Daniel Miller PMP · 07/26/24

https://community.smartsheet.com/discussion/comment/446566#Comment_446566

Thanks so much Nic. Works perfectly!

Paul Newcome · 07/29/24

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

Daniel Miller PMP · 07/29/24

https://community.smartsheet.com/discussion/comment/446677#Comment_446677

Thanks!

Risk Rating Formula

Best Answers

Answers

Help Article Resources

Categories