Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have site column with the following formula:

[AmountLeftToPay]/([AmountAssigned]-[AmountReleased])

The problem is the division by zero, sometimes it shows on list items #DIV/0!

So I wanted to do the following: =IF([AmountAssigned]-[AmountReleased]=0; 0; [AmountLeftToPay]/([AmountAssigned]-[AmountReleased]))

And it shows me the same error in all lists where its used. is not supported. /apps/xx/xx/Lists/Budgets : The formula contains a syntax error or is not supported.

If I change ; with commas, The formula contains a syntax error or is not supported.

so I got no clue

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

SharePoint uses Excel formulas (and same engine to parse them) for calculated fields. And as you probably know, Excel uses localized formulas.

Thus, when trying to define formulas through GUI and having non-english site, you should use the localized version of function names and ";" instead of commas. This definitely brings some confusion, because this fact is not even mentioned in documentation I know.

But from code or from powershell it is sometimes possible to put english formulas there (your thread should be set to use English locale or something like this). Also, internally SharePoint stores those formulas in English format.

So to resolve your issue, just open Excel, create a sketch of your formula there using formula wizard, and then "migrate" it to SharePoint, passing correct field names instead of Excel cell addresses (please keep in mind that not all of the Excel functions will work in SharePoint, only limited subset of them is available).

Hopefully this explanation makes things a bit more clear for you.

share|improve this answer
add comment

Sharepoint bug? who knows

when changing it via powershell with the same formula above it works

share|improve this answer
add comment

You have to write formula like

=IF(ISERROR([Column1]/[Column2]),"NA",[Column1]/[Column2])

Returns NA when the value is an error

Hope it helps

Regards,

Dhaval

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.