Re: [office-comment] OpenFormula: Are spreadsheet makers willingto switch to Excel semantics?

["David A. Wheeler" <dwheeler@dwheeler.com>]

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.

>  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".

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.

> 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.

Microsoft intentionally copied the general semantics from Lotus 1-2-3,
which copied the semantics from VisiCalc.   VisiCalc, of course,
based its semantics on standard mathematical notation, as well as
the FORTRAN/BASIC methods of ASCII-izing them. This is nothing
new; there's a general conservatism in semantics for spreadsheets.


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

Yes.  Indeed, I don't intend to specify precision levels
(though in practice most people will use 64-bit IEEE doubles or better).

--- David A. Wheeler