Mixing decimal numbers in Calc

Discuss the spreadsheet application

Mixing decimal numbers in Calc

Postby Benoitch » Sun May 26, 2019 5:07 pm

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 Sierra 10.12.6
Benoitch
 
Posts: 2
Joined: Sun May 26, 2019 4:56 pm

Re: Mixing decimal numbers in Calc

Postby Zizi64 » Sun May 26, 2019 7:44 pm

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; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7961
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Mixing decimal numbers in Calc

Postby Benoitch » Sun May 26, 2019 11:34 pm

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 Sierra 10.12.6
Benoitch
 
Posts: 2
Joined: Sun May 26, 2019 4:56 pm

Re: Mixing decimal numbers in Calc

Postby RusselB » Mon May 27, 2019 3:31 am

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.6 and LibreOffice 6.0.6.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
Volunteer
 
Posts: 5075
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Mixing decimal numbers in Calc

Postby Zizi64 » Mon May 27, 2019 6:50 am

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; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7961
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 10 guests