SharePoint, Technical

Calculated Column Nested Function Limit

Most sites that have posted on the nesting function limit for calculated columns in SharePoint (at least the one’s I found) state that the limit for nesting is eight. Those sites include Intro to Data Calculations on Microsoft.com. My experience shows it’s higher than eight, but not as high as I need it. Sad smile

Create a list and add a column for “Likelihood” and a column for “Consequence”, each a number field. Now add a “Score” column as a calculated column and use the below as the formula:

=IF(AND(Likelihood=1,Consequence=1),1,IF(AND(Likelihood=1,Consequence=2),3,IF(AND(Likelihood=1,Consequence=3),6,IF(AND(Likelihood=1,Consequence=4),10,IF(AND(Likelihood=1,Consequence=5),15,IF(AND(Likelihood=2,Consequence=1),2,IF(AND(Likelihood=2,Consequence=2),5,IF(AND(Likelihood=2,Consequence=3),9,IF(AND(Likelihood=2,Consequence=4),14,IF(AND(Likelihood=2,Consequence=5),19,IF(AND(Likelihood=3,Consequence=1),4,IF(AND(Likelihood=3,Consequence=2),8,IF(AND(Likelihood=3,Consequence=3),13,IF(AND(Likelihood=3,Consequence=4),18,IF(AND(Likelihood=3,Consequence=5),22,IF(AND(Likelihood=4,Consequence=1),7,IF(AND(Likelihood=4,Consequence=2),12,IF(AND(Likelihood=4,Consequence=3),17,IF(AND(Likelihood=4,Consequence=4),21,”Unknown”)))))))))))))))))))

SharePoint will save the calculation just fine. Count the parens at the end there…19. Now, if you add one more, blammo:

=IF(AND(Likelihood=1,Consequence=1),1,IF(AND(Likelihood=1,Consequence=2),3,IF(AND(Likelihood=1,Consequence=3),6,IF(AND(Likelihood=1,Consequence=4),10,IF(AND(Likelihood=1,Consequence=5),15,IF(AND(Likelihood=2,Consequence=1),2,IF(AND(Likelihood=2,Consequence=2),5,IF(AND(Likelihood=2,Consequence=3),9,IF(AND(Likelihood=2,Consequence=4),14,IF(AND(Likelihood=2,Consequence=5),19,IF(AND(Likelihood=3,Consequence=1),4,IF(AND(Likelihood=3,Consequence=2),8,IF(AND(Likelihood=3,Consequence=3),13,IF(AND(Likelihood=3,Consequence=4),18,IF(AND(Likelihood=3,Consequence=5),22,IF(AND(Likelihood=4, Consequence=1),7,IF(AND(Likelihood=4,Consequence=2),12,IF(AND(Likelihood=4,Consequence=3),17,IF(AND(Likelihood=4,Consequence=4),21,IF(AND(Likelihood=4,Consequence=5),24,”Unknown”))))))))))))))))))))

Results in an error “Cannot complete this action. Please try again.” Go ahead…try again. Just kidding, don’t bother. So it looks like the limit is 19. I’ve tried this in two separate environments, but please post a comment if you see different results.

Edit (Solution): Here’s how I solved it, by the way. Split it out and “&”ed them together.

=IF(AND(Likelihood=1,Consequence=1),1,IF(AND(Likelihood=1,Consequence=2),3,IF(AND(Likelihood=1,Consequence=3),6,IF(AND(Likelihood=1,Consequence=4),10,IF(AND(Likelihood=1,Consequence=5),15,IF(AND(Likelihood=2,Consequence=1),2,IF(AND(Likelihood=2,Consequence=2),5,IF(AND(Likelihood=2,Consequence=3),9,IF(AND(Likelihood=2,Consequence=4),14,IF(AND(Likelihood=2,Consequence=5),19,IF(AND(Likelihood=3,Consequence=1),4,IF(AND(Likelihood=3,Consequence=2),8,IF(AND(Likelihood=3,Consequence=3),13,IF(AND(Likelihood=3,Consequence=4),18,IF(AND(Likelihood=3,Consequence=5),22,””)))))))))))))))&IF(AND(Likelihood=4,Consequence=1),7,IF(AND(Likelihood=4,Consequence=2),12,IF(AND(Likelihood=4,Consequence=3),17,IF(AND(Likelihood=4,Consequence=4),21,IF(AND(Likelihood=4,Consequence=5),24,IF(AND(Likelihood=5,Consequence=1),11,IF(AND(Likelihood=5,Consequence=2),16,IF(AND(Likelihood=5,Consequence=3),20,IF(AND(Likelihood=5,Consequence=4),23,IF(AND(Likelihood=5,Consequence=5),25,””))))))))))

Leave a Reply

Your email address will not be published. Required fields are marked *