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

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    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