SharePoint 2013 calculated column adding multiple if statement

Question

i created a calculated column(column name"status") with a simple formula below:

=if([Priority]="Low"), "Yellow", if([Priority]="Normal"),"Green", if([Priority]="High", ”Red")))

As you can see, what I want to achieve is that, when the "Priority" column = "Low", then the "status" column = "Yellow", and when it is ="Normal", the "status" column = "Green". The formula looks to me is correct, but when i was trying to save the page, i always got an error message with the following message:

"Sorry, something went wrong The formula contains a syntax error or is not supported."

Can you please help?

I've created a formula with multiple IF conditions to calculate the shift of our production based on a data entry from an operator. However, I get the same error as mentioned above: "Sorry, something went wrong The formula contains a syntax error oris not supported." Formula: =IF(TEXT(Created,"hh:mm AM/PM"<”06:10 AM”,"3rd",IF(TEXT(Created,"hh:mm AM/PM">”02:09 PM”,IF(TEXT(Created,"hh:mm AM/PM"<”10:10 PM”,"2nd","3rd"),"1st"))) I tried re-writing it in notepad but didn't work either. It did work in excel and I more or less based the formula off of that after I made it work in excel in the first p — user88396, Jan 10 '20 at 02:17

score 2 · Answer 1 · answered Jun 20 '17 at 06:19

2

There were two problems with your formula.

The first one that you missed to change the character left to "Red" to a quotation mark. The difference is tiny, but it is there. ”Red"

Second problem were that you were closing each condition, rendering the last closing brackets useless.

=if([Priority]="Low", "Yellow", if([Priority]="Normal","Green", if([Priority]="High", "Red")))

answered Jun 20 '17 at 06:19

Christoffer

9,801
2
36
53

thanks for your help! you really have a pair of sharp eyes!!! saved my day – T Zhang Jun 20 '17 at 07:44

score 2 · Accepted Answer · answered Jun 20 '17 at 19:35

2

It helps when your write your Formula in a text editor (even Notepad will do) and add line-breaks and indents

=if([Priority]="Low")
 , "Yellow"
 , if([Priority]="Normal")
 , "Green"
 , if([Priority]="High", ”Red")
))

Where it should be

=if([Priority]="Low"
    , "Yellow"
    , if([Priority]="Normal"
         , "Green"
         , if([Priority]="High"
              , ”Red"
             )
        )
   )

On copy/paste to SP all whitespace will be removed (so retain that original Notepad file!)

Note: In a decent IDE this will also catch your wrong double-quote..

answered Jun 20 '17 at 19:35

Danny '365CSI' Engelman

21,176
7
35
79

Thank you, sir. I will follow you suggestion to write the formulas in the notepad and then copy/paste to SP. however, I encountered another error with my if statement ... checked over and again, and copied to Excel. it worked well in Excel but I always got an error in SharePoint. Can you please give me a clue?
=if([Today]-[Last Reviewed Month/Year]<=180,"Green",if(and([Today]- [Last Reviewed Month/Year]>180,[Today]-[Last Reviewed Month/Year] <365),"Yellow", if([Today]-[Last Reviewed Month/Year])>"365","Red"))) – T Zhang Jun 21 '17 at 07:45
[Today] will never work; see: https://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column/151336#151336. Please mark question as answered – Danny '365CSI' Engelman Jun 22 '17 at 18:41

SharePoint 2013 calculated column adding multiple if statement

2 Answers2

Linked