Forum Discussion

Dimitrij_P's avatar
Dimitrij_P
Copper Contributor
Sep 22, 2021
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...
Lists
SharePoint Online
  • RobElliott's avatar
    RobElliott
    Sep 22, 2021

    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)

RobElliott's avatar
RobElliott
Silver Contributor
Sep 22, 2021

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
Sep 13, 2023

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
    Sep 14, 2023

    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

    • ChristinaGSPAdmin's avatar
      ChristinaGSPAdmin
      Copper Contributor
      Oct 28, 2024

      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

       

       

      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Oct 28, 2024

        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.

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Sep 14, 2023

    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.

    • tomas_12's avatar
      tomas_12
      Copper Contributor
      Sep 14, 2023

      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

       

      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Sep 14, 2023

        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.

  • UA-HandCrafts's avatar
    UA-HandCrafts
    Copper Contributor
    Sep 13, 2023

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

Resources