Proposal: Improve definition of formula syntax

I'm delighted with much that I see in the
Committee Draft 2, 21 Dec 2004, of the
"Open Document Format for Office Applications (OpenDocument) 1.0".
It looks like much is being refined.

However, I'm still concerned that this specification fails
to include enough detail to permit interchange of even simple
spreadsheets with computed values.  Section 8.1.3 tries to
define the syntax of formulas, but while it gives some details,
it does not give enough syntax details to permit
interchange of trivial formulas between spreadsheet programs.
Too much is left undefined, such as what are the
set of operators and their representation (is "*" multiply?
What's exponentiation?), precedence, number format
(is "." or "," the decimal separator?), and so on.
If each spreadsheet program chooses different
incompatible representations, the result will be
unnecessary incompatibilities.

Thankfully, this is easily fixed, with a small addition of
material in the existing section defining formulas (section 8.1.3).

The attached file formula.sxw is about 2.5 pages of text that
I believe solves the problem.  You can see from the text
itself why I think the current text is inadequate.
Basically, there's a LOT of things that should be in
the spec but weren't. I had to figure out things by editing
Calc files, then unzipping them to see what they'd do.
Let's help make sure others don't need to do that!

For two programs to exchange spreadsheet functions, they
must both implement the functions used by the spreadsheet,
in the same way sufficient for that spreadsheet.
However, I believe a large number of spreadsheets don't need
complex support or many functions.  I've seen many
spreadsheets that could be handled quite nicely by a spreadsheet
that only implemented simple calculations (+, -, *, / between cells
with parentheses), the functions SUM(),
AVERAGE(), MIN(), MAX(), COUNT(), and IF(..) with comparators.
It'd be great if there was a standard for a large suite
of functions to support interoperability, but the first
step is to at least agree on the syntax of formulas and
define just a few functions sufficient for these mimimal
spreadsheets.  So, that's what I've supplied here.
It should be a simple cut & paste job, if you like it.

Hopefully this would be enough for at least simple
interoperation between OpenOffice.org, KOffice's spreadsheet
program, Gnumeric, etc... and Excel if they ever
implemented this.

I urge the committee to consider this, and I hope you'll
accept this change.

This is not a change in scope of the document; it's simply
a clarification of text ALREADY in the document.
One comment I received earlier was a concern that this would
define a "programming model", but I believe that that was a
misunderstanding.  This proposal does NOT define any particular
programming model.  This only defines a way to exchange formulas;
the functions that the formulas invoke may be programmable,
and the full formula language supported by an implementation
might be a fully programmable language,
but I'm treating all of that as out-of-scope.
The purpose of this text is to more clearly specify what's
already in the document, sufficient so you could actually exchange
spreadsheets in it.  The current text in 8.1.3 already
defines a specific, concrete syntax; it has to, to provide
any interoperability of formulas.  It just doesn't give
enough detail to actually meet its goal.

I submitted an older version of this on November 1, 2004,
but I didn't hear back any comments on it.  I'm guessing
that the comment was lost.  So, I'm resubmitting this
slightly modified version of that proposal.
The only change is that here I don't even TRY to give a
longer list of functions; I just list a very few functions
that should be implemented, and leave the idea of defining
a long list of functions to some future time (possibly a
separate project).

For your amusement, I've also attached a .y and .l file
that I created.  To create this text, I created programs in
yacc & lex to make sure that the syntax was defined precisely,
and then wrote the text to match... this makes the text far
more precise.  You don't need to DO anything with the .y and .l
files, I'm not suggesting that they be in the document... but I
thought you might find them of interest.

Please let me know if this was received, and/or if there
are objections I could respond to about it.

--- David A. Wheeler