Mixing decimal numbers in Calc

Discuss the spreadsheet application
Post Reply
Benoitch
Posts: 3
Joined: Sun May 26, 2019 4:56 pm

Mixing decimal numbers in Calc

Post by Benoitch »

Hi,

I regularly have issues with numbers formatting. I mainly use the French (Canada) locale (as is my OS as well). But I set the numbers in cell to be English (Canada), to follow a more natural format for the numbers using a '.' (dot) instead of the comma. If I use 2 cells with numbers such as 123.45 and/or 123,45 I can sum these cells without any trouble. Mixing them works just fine.

However, if I use a formula like "=A2+B2" then it gets ugly.

A2=123.45
B2=123.45
C2=A2+B2 = 246.90

A2=123,45 (notice the comma here)
B2=123.45
C2=A2+B2 = 246.90

C2=123.45+123.45 = #NAME?
C2=123,45+123,45 = 246.90

I cannot find a way to seamlessly make it work. It's confusing cause I can "almost" always use the 'dot' notation when entering numbers (or currency), but when a put in an explicit addition ('+'), I have to use the comma where everywhere else it's the dot.

Do anyone have an idea how to fix that ???

Thanks in advance :)
NeoOffice 2017.15 On OSX Mojave 10.14.6
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Mixing decimal numbers in Calc

Post by Zizi64 »

You can format the RESULTS and the ENTERED constant NUMBERS in a Cell differently even in same spreadseet file. You can use different decimal separator for the cells: comma or dot depended on the applied cell style. The number will stored generally in a 8byte pouble precision format, independently to the format code.

But you must hit the default separator key for input of a constant in a formula. If you use irrelevant decimal separator in a FORMULA, then they will recognised as a TEXT (instead of numeric values)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Benoitch
Posts: 3
Joined: Sun May 26, 2019 4:56 pm

Re: Mixing decimal numbers in Calc

Post by Benoitch »

Thanks. Yeah I figured that much out. I was just wondering if there is a way to change that default separator in a FORMULA. Aside from changing the local altogether which is not quite interesting as it also change everything else (dates, etc).

Thanks for your time :)
NeoOffice 2017.15 On OSX Mojave 10.14.6
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Mixing decimal numbers in Calc

Post by RusselB »

I don't think this can be done, as, for example, how would it know if 1,234 is supposed to be 1,234.000 (to give the number 3 decimal places) or if it's supposed to be 1.234?
Now if there was a . in the number (eg: 1,234.75), then it could be set to ignore that in the , & . exchange as a decimal is detectable.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Mixing decimal numbers in Calc

Post by Zizi64 »

I was just wondering if there is a way to change that default separator in a FORMULA.
Yes, there is such setting (Locale). But is will change the decimal separator, generally. You can not adjust it cell-by-cell differently.
And you must re-enter (re-edit) all of your formulas with the new decimal separator, because this behavior is related to the number recognizing procedure of the data input. And because all of such data that was recognized as text, they still as text.
The Calc will repeat the number recognizing procedure only at the rentering (or at the Ctrl-X, Shift-Ctrl-V) of the data.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply