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
Mixing decimal numbers in Calc
Mixing decimal numbers in Calc
NeoOffice 2017.15 On OSX Mojave 10.14.6
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)
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.
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.
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).
Thanks for your time
Thanks for your time
NeoOffice 2017.15 On OSX Mojave 10.14.6
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.
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.
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.
Re: Mixing decimal numbers in Calc
Yes, there is such setting (Locale). But is will change the decimal separator, generally. You can not adjust it cell-by-cell differently.I was just wondering if there is a way to change that default separator in a FORMULA.
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.
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.