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

Find more posts tagged with

Accepted answers

Jason Tarpinian

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.

All comments

Jason Tarpinian

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.

cnikkih

Thank you!!! That worked perfectly.

Paul Newcome

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:

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.