Ask Your Question
0

Adding numbers with commas and dollar signs in a single cell

asked 2018-12-15 22:16:08 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag flag offensive close merge delete

Comments

This is a "forum" for users. Your query aims to the developers of the software. Check the https://bugs.documentfoundation.org/

Grantler gravatar imageGrantler ( 2018-12-16 10:23:45 +0200 )edit

It's not a bug though. And it's useless to point someone with a "why" question to the bug tracker.

erAck gravatar imageerAck ( 2018-12-16 19:27:19 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-16 15:58:14 +0200

Lupp gravatar image

updated 2018-12-16 16:05:48 +0200

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.

edit flag offensive delete link more

Comments

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.

erAck gravatar imageerAck ( 2018-12-16 19:21:38 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-12-16 19:27:28 +0200 )edit

We had that already and got enough complaints about it. Not going back that route.

erAck gravatar imageerAck ( 2018-12-16 20:07:34 +0200 )edit

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: https://ask.libreoffice.org/en/questi...

Lupp gravatar imageLupp ( 2018-12-16 21:12:43 +0200 )edit

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).

rlk gravatar imagerlk ( 2018-12-18 20:35:26 +0200 )edit

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.

rlk gravatar imagerlk ( 2018-12-18 20:47:29 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-12-18 21:07:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-15 22:16:08 +0200

Seen: 61 times

Last updated: Dec 16 '18