Calculated Field IF expression separators

Question

I'm creating a list with some calculated fields from code that use IF expressions.

fieldCalc.Formula = "=IF([Field1]=0,[Field2],[Field3]&\" (\"&([Field4]/[Field5]*100)&\"%)\")";

Apparently, when used in a SharePoint enviroment with comma for decimal point (like Slovenian), the separators in IF expression must be changed to semicolon, like so:

fieldCalc.Formula = "=IF([Field1]=0;[Field2];[Field3]&\" (\"&([Field4]/[Field5]*100)&\"%)\")";

How do I know what separator to use in my expressions?

Answer 1

Formulas and functions depend on the language of your site. It's not just about commas vs. semi-colons, the function names will change too.

You can play with this example:

http://sp2010.pathtosharepoint.com/SharePoint-User-Toolkit/Pages/Color-Coding-Calendar-List.aspx

Change the language, and you'll see how it affects the formula. For example, "IF" will become "ALS" in Dutch.

My examples confirm your rule of thumb: if the comma serves as decimal point in numbers, then use a semi-colon.

Answer 2

Try using semi colons by default, i think it should work with them regardless of the language you select.

Answer 3

I think that you need first look the beheavior on the Column Setting.

Look this example on my list:

My native language on site is spanish.

=IF([Adherencia Metodologica Valor]="";" ";IF([Adherencia Metodologica Valor]<10;""&[Adherencia Metodologica Valor]&"%";IF([Adherencia Metodologica Valor]>9;""&[Adherencia Metodologica Valor]&"%";)))

I render the HTML content... but this is other thing :)