[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [office-comment] OpenFormula: Are spreadsheet makers willing to switch to Excel semantics?
I'm not sure if this list is the right place to continue the OpenFormula discussion, but apparently this seems to continue without much objection. So I'll just tag along with a comment to David's enthusiasm. I'll take it that OO is "hosting" the OF discussion :) I think you appear to be pressing on all the sensitive points here through a lot of hard work. As you said, the Excel formula isn't quite documented, at least to the outside world. Would the aim (of OF) then be to reverse-engineer Excel's formula and semantic behavior and serialize it into a open specification? As admirable as all your hourless investigation seems, it would seem to be not very productive, nor even feasible, to perform such a task. You did some work on identifying boolean, string and numerical conversions, but what about conversions between pairs of all other types? To be complete, one would need to find out the actual behaviors of all position-ordered pairs for all operators (eg. string+error, error+string, boolean+float, date+string, date+integer, etc), which may not be commutative due to types of parameters even when mathematically they should be. Further, assuming that it's done, the release of another newer version of Excel will render the spec "incompatible" again. Please don't get me wrong; I only hope to share this concern about the approach that you seem to have taken, and don't wish to see that your hours are wasted (in my opinion). I'm happy to be corrected, but what I see now at least is that if we have a "proper" OpenFormula, one which has an objective of ensuring proper exchange of formulas with mathematical correctness, and leave the onus of implementing this serialized format to the software/applications, then the value-add for the OpenFormula would be that senders & receivers know the formulas encoded will be properly "rendered" into expected mathematical functions in their local systems. With that then, it wouldn't matter whether sender is a low-end 486 running older version of Excel, and receiving side is an open-office spreadsheet software with software-supported high-precision libraries, the formulas will be understood properly and calculations will be done correctly (or else errors flagged correctly). Isn't that what we want to see for OpenFormula? Best Regards, Chin Chee-Kai SoftML Tel: +65-6820-2979 Fax: +65-6743-7875 Email: cheekai@SoftML.Net http://SoftML.Net/ On Tue, 22 Feb 2005, David A. Wheeler wrote: >>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 >>