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

[Chin Chee-Kai <cheekai@softml.net>]

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