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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!