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 i...
Lists
SharePoint Online
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