Risk Rating Formula
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 
Best Answers

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

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

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

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.

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

Thanks so much Nic. Works perfectly!

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

Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!