[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?**

*From*:**Chin Chee-Kai <cheekai@softml.net>***To*: "David A. Wheeler" <dwheeler@dwheeler.com>*Date*: Thu, 24 Feb 2005 11:47:50 +0800 (SGT)

On Wed, 23 Feb 2005, David A. Wheeler wrote: >>Chin Chee-Kai wrote: >>> 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? >> >>Essentially yes, at least in the sense that any >>current Excel user should have a simple path to transition >>to it, while having their spreadsheets continue to work. I can see some interesting aspects of the use of the results of such investigative findings. And I think there may even be a need for this. However, if this is the objective, I'd think it'd fit more into something like OpenExcel than OpenFormula. >>> 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. >> >>That's correct. It turns out not to be as hard as you'd think. >>There are VERY few types. It turns out that "date" isn't >>a separate type; it's just a number (with a special format). >>So you have number, string, error, boolean, and "array". Again, with all acknowledgement of the laborious task this takes, I'd be worried about the "authoritativeness" (sorry, lack of a better word that is not a length phrase). This is not so much a challenge to your understanding but an innocent question, that if one should ask, for example, "how do you *know* for sure that dates are internally recognized as numbers with different formats and not a 3-dimensional point like (yyyy,mm,dd)?". Would some obscure but actual number/date fall into the conversion traps that number-->date-->number' will be a different number? Are errors like #VALUE! and #DIV/0! treated equivalently? All the time, or depending on how and when these errors are generated? How do we know for sure? That's just some of them. Questions like that may undermine the comfort level of users trying to think that the formulas have been "exchanged" but their results weren't. >>It's NOT easy, and I won't be able to do it by myself. >>But I can get it started. >> >>And the semantics do not need to be IDENTICAL to Excel... >>just enough similar so that people can exchange spreadsheets. An OpenFormula that focuses of mathematical correctness for interoperability of formulas could do just like that, with similar syntactical tokens as Excel so it ends up looking like Excel's formula, but the focus isn't anymore to "mirror" Excel's behaviors than to focus on correctness in results interchange. >>> Further, assuming that it's done, the release of another >>> newer version of Excel will render the spec "incompatible" again. >> >>Actually, no. If Microsoft changes the semantics, someone's >>spreadsheet will fail to work, and since they're used for budgets & >>other things involving real money, there's strong incentive to >>NOT change a thing. If a spreadsheet binary comprises only formulas, then you're probably right. But a spreadsheet is much more than just formulas. So meta information about versions, conversion methods, precedences, etc are not stored as part of formulas but in other parts of the same spreadsheet. While compatibility across version upgrades of spreadsheets can be detected and the necessary "upgrade formula conversion" routines be triggered (due to information outside of formulas themselves), one cannot detect version upgrades through just looking at a given formula alone. The upgraded, converted formulas are then optionally stored in new binary formats. Nothing should stop software makers and developers from changing for the better, leveraging on advanced hardware, adding functionalities and upgrades, as long as proper care and conversions are built in to allow preservation and continuance of user data. So I'm not so worried about end users of spreadsheets. But an OpenFormula tied to that would die of incompatibility almost at the time such upgrade availabilities are "announced". >>> 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? >> >>Yes. Indeed, I don't intend to specify precision levels >>(though in practice most people will use 64-bit IEEE doubles or better). It depends on the objective of doing OpenFormula, or OpenExcel for that matter :) Best Regards, Chin Chee-Kai SoftML Tel: +65-6820-2979 Fax: +65-6743-7875 Email: cheekai@SoftML.Net http://SoftML.Net/

**Follow-Ups**:**OpenFormula: Very first (rough) draft***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**References**:**Re: [office-comment] OpenFormula: Are spreadsheet makers willingto switch to Excel semantics?***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

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