Formula for ITIL Prioritization Matrix

cnikkih

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

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.

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: