Forum Discussion
Dimitrij_P
Sep 22, 2021Copper Contributor
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 = calculated column
The formula looks like this
=IF([Column 1] = 3; "OK"; "NOK")
After the confirmation, I get a message of a syntax error.
I don't know where the error should be, since the formula is quite simple.
Do you have a hint?
Thank you in advance,
best regards
Dimitrij
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)
- AlexinEdmontonCopper Contributor
Hello all just wanted to throw in my comments that I think sometimes the formula required a semicolon and sometimes it requires a comma, based on the default language of your environment. In my case the default environment was French and requires a semicolon. I believe other sites have other default languages, so their formulas required commas. This is really painful to troubleshoot and documentation seems pretty lacking in this regard from what I can tell.
- cathyacbikerCopper ContributorAlexinEdmonton Thank you problem solved 😄
- pieterheemeryckBrass ContributorThank you!! It was exactly this that caused my error. Very similar requirement: going from an enum value to display names. Belgian user, Dutch SPO site, ";" needed instead of ",".
- Frank_RombautCopper Contributor
- valderes-squadraCopper ContributorHi all.
I've the same problem. I tried replacing ; by , I tried with another browsers. 😞 - RobElliottSilver Contributor
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)- tomas_12Copper Contributor
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%",))))))))- UA-HandCraftsCopper Contributor
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 - Simplify complex formulas to one or two conditions.
- Dimitrij_PCopper ContributorHi Rob,
thank you for your quick reply!
I've tried your solution, unfortunately, it doesn't help. Still get the syntax error.
Dimitrij- RobElliottSilver Contributor
It works perfectly for me, so what is the formula you have used?