Using IF AND OR Formulas
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
Best Answer
-
=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
Answers
-
=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
-
That worked absolutely fine. Thanks,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!