Forum Discussion
Solved
Sharepoint list, Calculated Column, IF syntax error
Hi all, I have the following problem: I have a SharePoint list with different columns. Column 1 = single choice column with drop-down and values 1, 2, 3 Now I want to create a Column 2 = calcu...
Dimitrij_P it needs to be =if([Column 1]="3","OK","NOK")
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
Hello there, could you please give me a help hand here?
I dont know where is the issue, its little more complikcated formula..
Its all about one collumn, basically there are few conditions
If the number in collumn 1 is > 6950000 then in new calculated collumn should be 40% and etc.
=IF([collumn1]<6950000,"0%",
IF(AND([collumn1]>=6950000,[collumn1]<7050000),"40%",
IF(AND([collumn1]>=7050000,[collumn1]<7150000),"50%",
IF(AND([collumn1]>=7150000, [collumn1]<7200000),"60%",
IF(AND([collumn1]>=7200000, [collumn1]<7250000),"70%",
IF(AND([collumn1]>=7250000, [collumn1]<7350000),"80%",
IF(AND([collumn1]>=7350000, [collumn1]<7500000),"90%",
IF([collumn1]>=7500000,"100%",
)
)
)
)
)
)
)
)
It's always difficult to try to help from a distance. It seems like you have a chain of formulas and some of them are complex.
In such a case, I try to follow these steps:
- Simplify complex formulas to one or two conditions.
Starting with a simpler version of the formula can help isolate the issue. If the simplified formula works, then you can gradually add more conditions to identify at what point it breaks. - Replace the calculated values step by step with hard values.
Replacing calculated values with hard-coded values can help determine if the issue is with the calculations or with the formula structure itself. - When all the calculated values have been replaced and the final simplified formula still doesn't work, I check the data types of each column to make sure I have the right format.
Checking the data types of each column involved in the formula is crucial, as mismatched data types can lead to errors. - Finally (especially in Sharepoint) I play around with "," ";", etc.
SharePoint can be sensitive to the use of commas and semicolons, especially if the environment is set to a language other than English (like German, Hindi, Bangla, Tamil, Marathi, Punjabi, etc.)
If you haven't tried these steps yet, please do - it will get you to the root cause in 95% of cases.
Regards and good luck
Dimitrij
Hello, I'm dusting off this thread. I need some help with a calculated column on a SharePoint 2019 on prem task list. This is the formula I have and it should work, but it doesn't. I'd appreciate any and all help
ChristinaGSPAdmin Try using this formula:
=IF(OR([Choice Type]="Choice Type 1",[Choice Type]="Choice Type 2",[Choice Type]="Choice Type 3"),"Optional","Not Optional")Reference: Calculated Column Syntax Error
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.
ChristinaGSPAdmin
Hi Christina,
have you tried the step Nr. 4 in my latest post:
Finally (especially in Sharepoint) I play around with "," ";", etc.
SharePoint can be sensitive to the use of commas and semicolons, especially if the environment is set to a language other than English (like German, Hindi, Bangla, Tamil, Marathi, Punjabi, etc.)The formula you've used seems to be structured correctly, but let's review it for common issues in SharePoint calculated columns, particularly in SharePoint 2019.
Column Name Syntax:
- Make sure the column name [Choice Type] matches exactly as it appears in your list, including any spaces. SharePoint column names are sensitive to exact formatting.
Choice Column Values:
- Double-check that "Choice Type 1", "Choice Type 2", and "Choice Type 3" exactly match the options available in the "Choice Type" column. Even a small typo or extra space can cause the formula to fail.
Formula Validity:
- Sometimes, SharePoint can be a bit picky with the syntax, especially with OR conditions. You can try simplifying or testing one condition at a time to see if the formula evaluates correctly.
Data Type of Result:
- Ensure that the data type you selected for the result (in your case, "Single line of text") is compatible with what the formula returns.
Possible Debugging Steps
To isolate the issue, try the following simplified formula to test each condition individually:
=IF([Choice Type]="Choice Type 1","Optional","Not Optional")If this works, add each condition back one at a time to identify where the problem might be occurring.
Reg.
Dimitrij
- Thanks! SharePoint does not like OR, at all. That's where the sticking point is. What do you recommend as a way forward? Two calculated columns referencing each function or is there a way to do all of this for one calculated column. I'd appreciate any guidance.
