Forum Discussion

Dimitrij_P's avatar
Dimitrij_P
Copper Contributor
Sep 22, 2021

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)

  • AlexinEdmonton's avatar
    AlexinEdmonton
    Copper 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.

  • Hi all.

    I've the same problem. I tried replacing ; by , I tried with another browsers. 😞
  • RobElliott's avatar
    RobElliott
    Silver 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_12's avatar
      tomas_12
      Copper Contributor

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

       

      • UA-HandCrafts's avatar
        UA-HandCrafts
        Copper Contributor

        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

    • Dimitrij_P's avatar
      Dimitrij_P
      Copper Contributor
      Hi Rob,
      thank you for your quick reply!

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

      Dimitrij
      • RobElliott's avatar
        RobElliott
        Silver Contributor

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

Resources