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

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

*From*:**"David A. Wheeler" <dwheeler@dwheeler.com>***To*: dwheeler@dwheeler.com*Date*: Tue, 22 Feb 2005 19:35:22 -0500 (EST)

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: http://www.dwheeler.com/openformula/testss.xls 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

**Follow-Ups**:**Re: [office-comment] OpenFormula: Are spreadsheet makers willing to switch to Excel semantics?***From:*Chin Chee-Kai <cheekai@softml.net>

**References**:**Public Comment***From:*comment-form@oasis-open.org

**OpenFormula (OpenOFormula?)- I'm working on a draft.***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**Re: [office-comment] OpenFormula (OpenOFormula?)- I'm working ona draft.***From:*Gary Edwards <garyedwards@yahoo.com>

**Formulas: "," and ";" as function parameter separators***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**Re: [office-comment] OpenFormulas***From:*Gary Edwards <garyedwards@yahoo.com>

**OpenFormula - scope, other info resources?***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**OpenDocument - suggested tweaks for bibliography format***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

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