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)

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

 

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.

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

        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 😕

Resources