Apache OpenOffice Community Forum

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

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)

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

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.

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.