Sharepoint list, Calculated Column, IF syntax error

[Dimitrij_P]

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

[#2 · Accepted solution]

@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)

[#3]

Hi Rob,
thank you for your quick reply!

I've tried your solution, unfortunately, it doesn't help. Still get the syntax error.

Dimitrij

[#4]

It works perfectly for me, so what is the formula you have used?

[#5]

The same you've posted:
=if([Column 1]="3","OK","NOK")

[#6]

@Dimitrij_P are you sure? This is my list and the formula which gives exactly what you wanted:

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#7]

@RobElliott 

now it works - very strange - I've not changed anything.

However, it works - so thank you very much for your suppot!

Best Regards

Dimitrij

[#8]

Hi all.

I've the same problem. I tried replacing ; by , I tried with another browsers. :(

[#9]

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.

[#10]

@AlexinEdmonton 

Thanks, You solved my problem.

Kind regards,

Frankly 

[#11]

Thank 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 ",".

[#12]

@AlexinEdmonton Thank you problem solved :D

[#13]

@RobElliott 

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%",

              )

            )

          )

        )

      )

    )

  )

)

[#14]

@tomas_12 

Hi, try this

=IF([column1]<6950000, "0%", IF(AND([column1]>=6950000,[column1]<7050000), "40%", IF(AND([column1]>=7050000,[column1]<7150000), "50%", IF(AND([column1]>=7150000,[column1]<7200000), "60%", IF(AND([column1]>=7200000,[column1]<7250000), "70%", IF(AND([column1]>=7250000,[column1]<7350000), "80%", IF(AND([column1]>=7350000,[column1]<7500000), "90%", IF([column1]>=7500000, "100%")))))))))

[#15]

@tomas_12 If your column1 is of type "Number", use formula like below for your calculated column: 

=IF([column1]<6950000,"0%",IF(AND([column1]>=6950000,[column1]<7050000),"40%",IF(AND([column1]>=7050000,[column1]<7150000),"50%",IF(AND([column1]>=7150000, [column1]<7200000),"60%",IF(AND([column1]>=7200000, [column1]<7250000),"70%",IF(AND([column1]>=7250000, [column1]<7350000),"80%",IF(AND([column1]>=7350000, [column1]<7500000),"90%",IF([column1]>=7500000,"100%",""))))))))

Note:

1. Sometimes comma( , ) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon( ; ) instead of comma( , ).
2. Use correct display name of your SharePoint columns in above formula.
3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

---

Please consider giving a Like if my post helped you in any way.

[#16]

@ganeshsanap 
Hello,

sadly I tried your formula with right collumn name, but didnt work :\

About using ";" instead of "," - not sure if this is the case, other callculated collumns are with "," too.

"Sorry, something went wrong

The formula contains a syntax error or is not supported"

But thanks for your help, if you have anything else in mind, I will be greatful

[#17]

@tomas_12 What is the column/data type of your column 1?

Also, make sure you are returning the calculated column as single line of text here: 

---

Please consider giving a Like if my post helped you in any way.

[#18]

@ganeshsanap 

Collumn 1 is a calculated collumn, that adds up 3 different numbers together
collumn1 = collumnA+collumnB+collumnC (all numbers)

and its Currency format

2nd - Made sure that i want value to return as single line text, but didnt work too :\

[#19]

@tomas_12 

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:

1. 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.
2. 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.
3. 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.
4. 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