Forum Discussion
Calculated Column at SharePoint list
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:
- 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
- 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.