In Excel there is no problem doing something like:
=$1,234.56+$2,345.67 in a cell, or even
=1,234.56+2,345.67
but if I try to do that in LibreCalc, I get an error message number in the cell.
Why doesn’t this work in LibreCalc?
This is a “forum” for users. Your query aims to the developers of the software. Check the https://bugs.documentfoundation.org/
It’s not a bug though. And it’s useless to point someone with a “why” question to the bug tracker.
There is a long tradition of ignoring and undermining international standards in many countries, and in specific in software.
Nonetheless I would like to remind you of…
Quoting en.wikipedia.org on ISO 31-0: “Numbers consisting of long sequences of digits can be made more readable by separating them into groups, preferably groups of three, separated by a small space. For this reason, ISO 31-0 specifies that such groups of digits should never be separated by a comma or point, as these are reserved for use as the decimal sign. For example, one million (1000000) may be written as 1 000 000.”
We should start to relieve the mess, not aggravate it.
However, if I enter your example =1,234.56+2,345.67 into a Calc spreadsheet in LibO V 6.1.3 with user interface and locale set to English(UK), it is accepted and “recognised” as a valid formula and calculates to the correct result. The “illegal” grouping character is removed on the fly.
As far as I can see at least currency symbols are not accepted, fortunately.
If the function parameter separator was a , comma (which can be setup under Tools-Options) then using such group separator in a formula expression wouldn’t be accepted either.
Of course. Yes, I missed to think of that. The separator you mention is set to semicolon for my LibO, and I think this should definitely again be the default under all locales for the same reason for which ISO 31-0 deprecates point and comma as group separartors.
We had that already and got enough complaints about it. Not going back that route.
Yes, I assumed it was that way. However, there are to some degree reasonable and valid conplaints. And there are complaints due to being uninformed. Now we have the problem in the communities. Formulae posted by point-locale users don’t work if copied and pasted into a comma-locale user’s sheet.
Localisation on a syntactically relevant level is bad. Yes. I know there is the Excel compatibility…
See also: Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?
I probably should add that I am located in the US, although that might have been obvious by the $ usage. So if, as suggested, the calculation should work if locale is set to UK (without the $), then why would it not also work if locale is set to USA (which it is on my PC)? I also just did an internet search based on some of the comments above and found that within Excel there is a setting under Advanced called: “Use System Separators”, with choices for “Decimal Separator” (set to a period) and “Thousands Separator” (set to a comma). I’m guessing that is why there is no problem in Excel doing the in-cell calculation (but it does not explain why including the $ also works without issues in Excel).
Out of curiosity, I just changed locale to English (UK) and I got the same error (509) as before. So I’ve changed it back to US. Also, I am using version 6.1.3.2.
LibreOffice Calc simply doesn’t accept prefixed currency symbols when trying to recognise numeric constants in formulae…
Imo something like =$1,234.56+$2,345.67 isn’t a formula,but a bad joke, anyway. A spreadsheet software accepting it should be abandoned. You cannot know what follies it might introduce next without asking you.