incubator-ooo-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dennis E. Hamilton" <>
Subject RE: [DISCUSS][CALC] String content to numeric value
Date Fri, 27 Jul 2012 06:20:43 GMT
There are several matters here.

First, the rules for OpenFormula apply to literals as argument and to references to cells
where the value is of type text and the expected type is Number.

It does not apply to how *entered* values are converted.  In fact, there is no guidance at
all about how *entered* values are converted.  Likewise, how cell values are formatted for
the spreadsheet's presentation, for export in CSV, etc., are independent of these rules in

It strikes me that entry of values through an user interface is the time to deal with these
issues and that may resolve most cases.

For use of literals in formulas, there are ways for users to ensure that the desired interpretation
is made by explicit conversions.  The same conditions apply when a cell value is text and
it is intended to be used as a number.  Safe practice could be encouraged even if a given
product provides the intended result, since there is no assurance of consistent treatment
in an interchange situation.

(Multi-cell operandsare more complicated, when some cell entries in a range have values of
different type.)

Without too much thought, I image three things that can be done, along with user guidance:

 1. The rules for conversion of entered values based on the specified cell format need to
be clear, and that might also include understanding the cell display formatting in properly
handling the cell input conversion.  Since the storage of Number types is canonical and not
subject to locale considerations, this can isolate much of the problem.  The same applies
to export into non-OpenFormula forms, such as CSV.

 2. Users need to be cautioned about what happens when cells and literals are of different
types than the expected values.  It might also be useful for there to be warnings when text
for Number expected arises.  There can also be user guidance on how to detect and prevent
pathological cases.  

I recommend making the default behavior consistent with widespread practice, including consideration
of the importance of Excel interoperability.  If there seems to be no simple way out of it,
I think Error values are the appropriate response, so that an user is given the opportunity
to provide an explicit resolution rather than contend with silent discrepancies in their own
use or when their spreadsheet is interchanged.

 3. There are probably additional functions that could be introduced into OpenFormula, even
as implementation-specified extensions (in accordance with provisions for that), to provide
functions that will pass Numbers intact and will apply some formatting rule or function when
text is found instead.  (The conversion of cells of explicit date and time types is already
straightforward although it might need review.)  A few new functions may be sufficient to
achieve complete, straightforward control on behalf of users, including forcing of an Error
result.  The extensions could be agreed among the different implementations of OpenFormula,
including in Apache OpenOffice, LibreOffice, Gnumeric, Excel 2013, Google Docs, etc.  (But
first, it should be verified whether there are means enough already.)

 - Dennis

-----Original Message-----
From: Lei Wang [] 
Sent: Thursday, July 26, 2012 22:33
Subject: [DISCUSS][CALC] String content to numeric value

In ODFF standard 6.3.5 Conversion to Number,
If the expected type is Number, then if value is of type:
‚óŹ Text: The specific conversion is implementation-defined; an evaluator may
return 0, an Error value, or the results of its attempt to convert the Text
value to a Number (and fall back to 0 or Error if it fails to do so).
Evaluators may apply VALUE() or some other function to do this conversion,
should they choose to do so. Conversion depends on the actual locale the
application runs in, especially if group or decimal separators are involved.

It leave the implementation to decide whether to convert a text value to a
number, in which extent to convert a text value to a number. AOO now only
support convert string content to numeric, which is integer numbers
including exponent, and ISO 8601 dates and times in their extended formats
with separators. This cause issue 118942(

In my point of view it is better to support more scenarioes to convert a
text value to a number, such as decimal. Though different locale has
different decimal separator, AOO can support decimal separator for current
locale and standard decimal separator, ".". It will be more complicated for
date format.

What's your suggestion?

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message