Sharepoint list, Calculated Column, IF syntax error

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
  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:
  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
  - 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
  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:
  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( , ).
  Use correct display name of your SharePoint columns in above formula.
  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
    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
- 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%")))))))))
Dimitrij_P
Copper Contributor
Sep 22, 2021
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
  Silver Contributor
  Sep 22, 2021
  It works perfectly for me, so what is the formula you have used?
  - Dimitrij_P
    Copper Contributor
    Sep 22, 2021
    The same you've posted:
    =if([Column 1]="3","OK","NOK")

Forum Discussion

Sharepoint list, Calculated Column, IF syntax error

Resources