Re: [office-comment] Need more detailed definitions for formulas

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

Michael Brauer - Sun Germany - ham02 - Hamburg wrote:
> David,
> 
> formulas contained within tables or text fields are rather complex. 
> While it could be expected that where is a common set of functions and 
> operations that all office applications support, the actual set that is 
> implemented may and probably will vary from application to application, 
> if not even from application version to application version. Moreover, 
> most application support user defined functions that may be implemented 
> using certain, application specific methods, and that actually may 
> depend on the programming models of the applications.
> 
> In other words: Creating a specification for formulas contained in 
> office documents seems to be a very large effort, and the TC considered 
> this to be out of its scope, at least for the moment. Instead of this, 
> the TC specified that formulas should start with a namespace prefix. The 
> namespace that is associated with the namespace prefix uniquely 
> identifies the syntax and semantics of a formula, so that a converersion 
> of the formula becomes possible even if the formulas themselves are not 
> described in the OASIS Open Office specification, but somewhere else.

It sounds more like you're arguing FOR instead of AGAINST my statement.
If formulas currently vary so much between applications and
application versions that you can't be sure what they do, then
obviously there is a NEED to create a specification for a useful subset
that CAN be exchanged between applications and application versions.

Without specifying ANY syntax for the formulas, that means that
almost NO spreadsheet can be exchanged (except for the trivial ones
that only include constants).  This is supposed to be an
exchange format for office documents; a format that can't
exchange simple spreadsheets isn't very helpful.

Yes, user-defined functions and programming models will complicate
things greatly.  I'd even accept that that's something to be
standardized in the future, in some "version 2" or separate document.
But that's no reason to abandon all hope.

There's a simple solution here: identify a useful subset
that will meet most user's needs today, and then extend the
standard later to support the needs of sophisticated systems.

I disagree on the level of effort involved; you're basing
things on common mathematical statements, which have been
around rather a long time.  Just identify the set of
useful syntax and common functions available in all of
Lotus 1-2-3, VisiCalc, OpenOffice.org Calc, KDE Office,
Gnumeric, Excel, etc.

Just pick simple mathematics:
  constants, cell references, range references, label references
  binary operations (at least +, -, *, /, ^)
  unary operations (-, +)
  Common functions (say ~100 of them, like sum, average, max, min, if...)

I'd be willing to write a quick draft, by cribbing
OpenOffice.org documents for the functions, if you guys would
be willing to give it a fair shake.  I'm only willing if you guys
are willing to seriously consider including it in the document
(and give me credit for creating the draft).

One issue I've wondered about is formula naming conventions.
EXCEPT for the cell reference names, spreadsheets would present
to users exactly the same text that you store in this format.
The exception: cell references inside a table begin with ".".
It's annoying to have such an odd exception.  The problem is that
without the dot, you can't distinguish between label names and
cell names, and there are good reasons to have labels like "Q2".
So, though that's disquieting to me, I don't have a better solution.

--- David A. Wheeler