lists « Hannah's SharePoint

I’m currently working on a project where we needed to assign a priority to issues based on impact and urgency. You could always let users figure this out for themselves, but it’s much better to automate it when there are clear definitions for each value and a table which translates the impact and urgency to priority.

My first thought was: “Sure! I’ll do that! It’ll be simple.” Then I found myself looking at the calculated column formula screen and realized that it had been a long time since I had needed this knowledge.

The first limitation is that SharePoint only allows for up to 7 nested IFs. I broke the solution up into four calculated columns:

Priority – Very Urgent
Priority – Urgent
Priority – Not urgent
Priority – calculated

The first three columns calculate the correct priority value based on each urgency line. If the urgency is very urgent, the formula checks to see what the impact is and then sets the field value to the priority listed in the table. If the urgency in that issue does not match the formula (i.e. if the urgency is not very urgent), the field value is set to “N/A”, as that urgency is not applicable.

/* Very urgent */
=IF(AND(Urgency="Very Urgent",Impact="Critical"),"1",
IF(AND(Urgency="Very Urgent",Impact="High"),"1",
IF(AND(Urgency="Very Urgent",Impact="Medium"),"3",
IF(AND(Urgency="Very Urgent",Impact="Low"),"3",
IF(AND(Urgency="Very Urgent",Impact="Very Low"),"4","N/A" )))))

/* Urgent */
=IF(AND(Urgency="Urgent",Impact="Critical"),"1",
IF(AND(Urgency="Urgent",Impact="High"),"2",
IF(AND(Urgency="Urgent",Impact="Medium"),"3",
IF(AND(Urgency="Urgent",Impact="Low"),"3",
IF(AND(Urgency="Urgent",Impact="Very Low"),"4","N/A" )))))

/* Not urgent */
=IF(AND(Urgency="Not Urgent",Impact="Critical"),"2",
IF(AND(Urgency="Not Urgent",Impact="High"),"3",
IF(AND(Urgency="Not Urgent",Impact="Medium"),"3",
IF(AND(Urgency="Not Urgent",Impact="Low"),"4",
IF(AND(Urgency="Not Urgent",Impact="Very Low"),"4","N/A" )))))

To tie it all together, the Priority – calculated column looks at the values of all three columns and displays whichever one does not have the value “N/A”. If none of them have that, then the column displays the text “error”.

/* Final calculation */
=IF([Priority - very urgent]<>"N/A", [Priority - very urgent],
IF([Priority - urgent]<>"N/A", [Priority - urgent],
IF([Priority - not urgent]<>"N/A", [Priority - not urgent],
"Error calculating priority")))

To test, I showed all 6 applicable columns in a view:

You can see that because the urgency was very urgent and the impact was critical, a 1 was filled into the priority – very urgent column. When the Priority – calculated column did its work, it copied over the 1 from the priority – very urgent column.