## Mixing decimal numbers in Calc

### Mixing decimal numbers in Calc

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 ???

NeoOffice 2017.15 On OSX Sierra 10.12.6
Benoitch

Posts: 2
Joined: Sun May 26, 2019 4:56 pm

### Re: Mixing decimal numbers in Calc

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.

Zizi64
Volunteer

Posts: 8538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Mixing decimal numbers in Calc

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

NeoOffice 2017.15 On OSX Sierra 10.12.6
Benoitch

Posts: 2
Joined: Sun May 26, 2019 4:56 pm

### Re: Mixing decimal numbers in Calc

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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.

RusselB
Moderator

Posts: 5682
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Mixing decimal numbers in Calc

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.

Zizi64
Volunteer

Posts: 8538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary