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

Answers

  • 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

  • cnikkih
    cnikkih ✭✭✭✭

    Thank you!!! That worked perfectly.

  • Paul Newcome
    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:


    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com