0

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

2

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.

| improve this answer | |
0

Sharepoint bug? who knows

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

| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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