Formula for ITIL Prioritization Matrix
I need help with the formula for an ITIL Prioritization Matrix. Basically, we want to display P1, P2, etc, based on the Urgency and Impact shown below. I have come up with the formula below which come back unparseable and I can't figure out why. The second screenshot shows it in the sheet, and it appears to be properly validating. I made sure the values in the dropdown boxes in both columns were spelled correctly, including having no leading or ending spaces. I also changed them to text fields to see if that helped.
=IF(Urgency = "High", IF(Impact = "High", "P1", IF(Impact = "Medium", "P2", IF(Impact = "Low", "P3"))), IF(Urgency = "Medium", IF(Impact = "High", "P2", IF(Impact = "Medium", "P3", IF(Impact = "Low", "P4"))), IF(Urgency = "Low", IF(Impact = "High", "P3", IF(Impact = "Medium", "P4", IF(Impact = "Low", "P5"))))))
Above: Screenshot from sheet
Above: Screenshot of matrix
Best Answer
-
You'll need to include an AND() function in your IF().
=IF(AND(Urgency@row = "High", Impact@row = "High"), "P1",IF(AND(Urgency@row = "High", Impact@row = "Medium", "P2", IF(AND(Urgency@row = "High", Impact@row = "Low"), "P3"... repeat for whole table.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Answers
-
You'll need to include an AND() function in your IF().
=IF(AND(Urgency@row = "High", Impact@row = "High"), "P1",IF(AND(Urgency@row = "High", Impact@row = "Medium", "P2", IF(AND(Urgency@row = "High", Impact@row = "Low"), "P3"... repeat for whole table.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Thank you!!! That worked perfectly.
-
None of the column names in the first screenshot are highlighted which leads me to believe there is a misspelling in at least one of them. Otherwise the syntax from your original formula should work. I use the same method myself regularly to avoid using too many AND functions.
Proper Column Names:
Improper Name for Primary Column:
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives