OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: OpenFormula: Are spreadsheet makers willing to switch to Excelsemantics?

This is an OpenFormula question - any answers?

Are spreadsheet makers -- particularly OOo, but probably also
KOffice, Gnumeric, etc., willing to change the semantics
of their formulas to be compatible with Excel?
If so, shall I presume that the semantics should be equal
to Excel, and if not, then what?

Here's the background: Excel's semantics are essentially
undocumented, and as I work with them, I've realized that at least
OpenOffice.org 1.1.3's semantics are DIFFERENT than
Excel's.  This means that syntactically converted spreadsheets
can produce different answers in OOo, compared to Excel.

It's not a matter of "right" or "wrong"; what OOo is doing is reasonable.
But it's different than Excel, producing different results.
From the point-of-view of compatibility, they are compatibility bugs,
IF you believe that Excel semantics need to be matched
to enable easy transition and interoperation.

I have a test Excel spreadsheet at:
which shows some of the differences, e.g.:
* In Excel, the "+" tries to convert strings on either side
  into a number, and if successful, uses that number.
  In OOo, the "+" presumes that any string is 0.
  So formula =B2+B5, if B2 is the number 2 and B5
  is the label '11, produces 13 in Excel and 2 in OOo.
* In Excel, Logical values (TRUE, FALSE) are not considered
  numbers; they aren't counted by COUNT().  They ARE in OOo.
* In Excel, SUM(...) is NOT the same as num + num + num ...;
   SUM _only_ looks at values of type "numeric", ignoring
   strings and logical values.  In OOo, SUM(...) also ignores
   strings/text, but OOo converts any logical values to 0 and 1,
   so TRUE() adds one in OOo but not in Excel.
* In Excel, ISNUMBER(TRUE()) is false.
   In OOo, ISNUMBER(TRUE()) is true.
* ROMAN() works slightly differently, as I noted earlier,
   with TRUE() and FALSE() format parameters.
There also seem to be a few import problems with some values.

Now granted, this is a highly-stressing case, where I'm
intentionally LOOKING for trouble and semantic mismatches.
But it still makes the point.

If Excel's are the "right" semantics, then I can document
that "+" requires certain operations, etc., etc.
But if that's not the case, then I don't know what to document.
For functions, it's easy to define one function name as
doing one thing, another function name that does the other,
and then let Excel converters match the names up...
but that requires implementations of those functions,
and agreement on the names.

--- David A. Wheeler

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]