Issue 126656

Summary: formula: ="2,2"*2 produces #Value in cell
Product: Calc Reporter: lzrvpvl <lzrvpvl>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P5 (lowest) CC: marcelly.bernard, orcmid
Version: 4.1.2   
Target Milestone: ---   
Hardware: PC   
OS: Windows 10   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
sample of error none

Description lzrvpvl 2015-11-15 03:04:10 UTC
Created attachment 85133 [details]
sample of error

formula:    ="2,2"*2 produces #Value in cell
but         ="2"*2   produces correct value 4
Comment 1 orcmid 2015-11-15 04:10:38 UTC
 ="2.2"*2 doesn't work either.

"2.2" is a text value, not a number.  Apparently conversion ot to a number fails for text strings for non-integers.  

The OpenFormula specification of ODF is no help here.

It is perhaps of some interest that ="2.2"*2 does produce 4.4 in Excel 2016 when "." is the decimal separator of the current locale.
Comment 2 orcmid 2015-11-15 04:16:33 UTC
(In reply to orcmid from comment #1)
>  ="2.2"*2 doesn't work either.

It is a challenge when "2.2" is definitely text, as in 

A1 = "2.2"
B1 = A1*2

Having this work in interchange when the decimal separator is different depending on the locale where the spreadsheet is problematic.

 =VALUE("2.2")*2 works where "." is the decimal separator.
I suspect that 
 =VALUE("2,2")*2 works where "," is the decimal separator.
Comment 3 bmarcelly 2015-11-21 15:39:08 UTC
Yes, confirmed with Locale Setting French (France)
 =VALUE("2,2")*2 works where "," is the decimal separator.
And of course does not work with Locale setting English (USA).

Writing a formula like =VALUE("2,2")*2 is ambiguous: 
its result depends on the user Locale setting. Just change it and re-evaluate the formula, the result changes.

A more striking example : =VALUE("2,345")*2

Locale en-US gives value : 4690 because , is a thousands separator
Locale fr-FR gives value : 4.69 because , is a decimal separator (the value is displayed as 4,69)

And of course writing ="2,2"*2 has the same fundamental problem : it is an incorrect formula.
Comment 4 orcmid 2015-11-21 16:58:28 UTC
The fact that these conversions from text to numbers are locale-sensitive is part of the definition of OpenFormula.  Limiting automatic text->number conversion to integer cases is a way to avoid the interchange problems involved in the handling of separators in the text form.  The VALUE( ) operation is defined to be locale-dependent.

Conveying numbers via text values will have this problem in the absence of some way to know what the locale of the entry was.  There may be provisions for that in ODF.  I don't know that they are adequate for interchange across locales.

The best way to have locale-neutral handling of numbers is to have the numbers be entered into number-format cells.  The numeric value used for calculation is communicated in the document file in a local-independent format, regardless of how it was entered and how it is later displayed.  This goes for dates, times, and currency values also.  

An alternative *technical* approach is to use a "."-less, ","-less format.  For example, write ="22E-1"*2.  That is a text syntax that will convert correctly regardless of locale.  This is not something that users would likely do, and it leaves open the case of data obtained as already-prepared text values using locale-specific decimal and separator codes.

I wonder how this is handled in Canada and other bi-lingual communities.
Comment 5 orcmid 2015-11-21 17:10:55 UTC
As has been revealed in these comments, the behavior is by design.  

VALUE("v") conversion is locale-dependent.  For "v" (text) appearing where a number is required, OpenOffice only accepts values with no "," and "." characters.

Furthermore, the formulas and values in an OpenOffice Calc spreadsheet are conveyed in ODF OpenFormula format when stored in .ods files and when read from such files. 

It is clear there are usability issues as well as interchange pitfalls where "," and "." are involved.  The usability issued that inspired this bugzilla has to do with numbers carried in text not being interpreted as expected.  On the other hand, interpreting them in the locale of the reader can lead to serious incorrect treatment.

SHORT TERM: How do we provide guidance to users about how this works and what to watch out for?  Is it about explaining how #VALUE can arise and how to trouble-shoot those cases?

LONGER TERM: Much harder: What can we do to provide a better treatment that works between ODF implementations (i.e., has some level of "standard" support in ODF files between the different supporting products) and also works up/down versions of Apache OpenOffice?