Forum Discussion

FOzcan's avatar
FOzcan
Copper Contributor
Nov 26, 2024

Calculated Column at SharePoint list

I am trying to create a calculated column in my sharepoint list but I am having constant error after I added my code. I added the code below which I am using. What is my error and how can I correct it?

 

=IF(AND([Severity]=Insignificant, [Likelihood]=Very Low), "Level 1",
 IF(AND([Severity]=Insignificant, [Likelihood]=Low), "Level 1",
 IF(AND([Severity]=Insignificant, [Likelihood]=Medium), "Level 1",
 IF(AND([Severity]=Insignificant, [Likelihood]=High), "Level 1",
 IF(AND([Severity]=Insignificant, [Likelihood]=Very High), "Level 2",
 IF(AND([Severity]=Minor, [Likelihood]=Very Low), "Level 1",
 IF(AND([Severity]=Minor, [Likelihood]=Low), "Level 1",
 IF(AND([Severity]=Minor, [Likelihood]=Medium), "Level 2",
 IF(AND([Severity]=Minor, [Likelihood]=High), "Level 2",
 IF(AND([Severity]=Minor, [Likelihood]=Very High), "Level 3",
 IF(AND([Severity]=Serious, [Likelihood]=Very Low), "Level 2",
 IF(AND([Severity]=Serious, [Likelihood]=Low), "Level 2",
 IF(AND([Severity]=Serious, [Likelihood]=Medium), "Level 3",
 IF(AND([Severity]=Serious, [Likelihood]=High), "Level 3",
 IF(AND([Severity]=Serious, [Likelihood]=Very High), "Level 3",
 IF(AND([Severity]=Significant, [Likelihood]=Very Low), "Level 3",
 IF(AND([Severity]=Significant, [Likelihood]=Low), "Level 3",
 IF(AND([Severity]=Significant, [Likelihood]=Medium), "Level 4",
 IF(AND([Severity]=Significant, [Likelihood]=High), "Level 4",
 IF(AND([Severity]=Significant, [Likelihood]=Very High), "Level 4",
 IF(AND([Severity]=Major, [Likelihood]=Very Low), "Level 4",
 IF(AND([Severity]=Major, [Likelihood]=Low), "Level 4",
 IF(AND([Severity]=Major, [Likelihood]=Medium), "Level 4",
 IF(AND([Severity]=Major, [Likelihood]=High), "Level 4",
 IF(AND([Severity]=Major, [Likelihood]=Very High), "Level 4", "")))))))))))))))))))))))))))))))

Lists
SharePoint Online

2 Replies

  • Rob_Elliott's avatar
    Rob_Elliott
    Silver Contributor
    Nov 27, 2024

    There is a limit of 19 nested IFs in a calculated column. Beyond that and you get the error.

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

  • Sethivi's avatar
    Sethivi
    Copper Contributor
    Nov 27, 2024

     

    Hello,

    Your formula contains several issues that can lead to errors in SharePoint. Below is an analysis of the problems and the corresponding solutions:

    Issues:

    1. Case Sensitivity for Text Values:
      • SharePoint column names and text values in calculated formulas are case-sensitive.
      • Always wrap text values in double quotes (") if they are not numeric. For example:
        Correct: [Severity]="Insignificant"
        Incorrect: [Severity]=Insignificant
    2. Formula Length Limitation:
      • SharePoint calculated column formulas have a character limit (approximately 1,200 characters). Your formula appears to exceed this limit.

    Corrected Formula:

    Here’s a shorter version of your formula that works within the SharePoint limits. This includes the maximum conditions that can fit before hitting the character limit:

     

    =IF(AND(Severity="Insignificant",Likelihood="Very Low"),"Level 1",IF(AND(Severity="Insignificant",Likelihood="Low"),"Level 1",IF(AND(Severity="Insignificant",Likelihood="Medium"),"Level 1",IF(AND(Severity="Insignificant",Likelihood="High"),"Level 1",IF(AND(Severity="Insignificant",Likelihood="Very High"),"Level 2",IF(AND(Severity="Major",Likelihood="Very Low"),"Level 4",IF(AND(Severity="Major",Likelihood="Low"),"Level 4",IF(AND(Severity="Major",Likelihood="Medium"),"Level 4",IF(AND(Severity="Major",Likelihood="High"),"Level 4",IF(AND(Severity="Major",Likelihood="Very High"),"Level 4",IF(AND(Severity="Significant",Likelihood="Very Low"),"Level 3",IF(AND(Severity="Significant",Likelihood="Low"),"Level 3",IF(AND(Severity="Significant",Likelihood="Medium"),"Level 4",IF(AND(Severity="Significant",Likelihood="High"),"Level 4",IF(AND(Severity="Significant",Likelihood="Very High"),"Level 4",IF(AND(Severity="Serious",Likelihood="Medium"),"Level 3",IF(AND(Severity="Serious",Likelihood="High"),"Level 3",IF(AND(Severity="Serious",Likelihood="Very High"),"Level 3",IF(AND(Severity="Serious",Likelihood="Low"),"Level 2")))))))))))))))))))

     

    Alternatives:

    If you need to include all the conditions from your original formula, consider using Power Automate or Power Apps. Both tools provide greater flexibility for complex logic and avoid SharePoint formula limitations.

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources