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

[Michael Brauer - Sun Germany - ham02 - Hamburg <Michael.Brauer@Sun.COM>]

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.

Best regards

Michael Brauer

OASIS Open Office TC chair.

David A. Wheeler wrote:
> Formulas need to be defined much better than they currently are.
> The current text isn't sufficient to meet the goal of an
> interchangeable data format for spreadsheets.
> It shouldn't be hard to fix this by expanding the definition.
> 
> Section 8.1.3 purports to define formulas, but other
> than saying that they often begin with "=", and an
> example using SUM(), it doesn't say much.
> 
> Section 15.1 is even worse; it implies that formulas
> are strings with no common syntax.  If that's true,
> then spreadspreads can't be interchanged using this
> format.  Which is both absurd and unnecessary, especially
> considering that so many other parts of the specification
> hinge on formulas.
> 
> This definition needs to be tightened up considerably.
> Spreadsheet interoperability hinges on getting a common
> definition of formulas.  For example, a common set of
> functions that are defined (and thus can be exchanged
> with confidence).
> 
> It will be long enough to need its own subsection
> (or maybe its own section!!).
> Here's a start:
> 
> For purposes of this specification,
> a formula represents a calculation
> that produces a single numeric answer.
> Formulas are widely used, for example, in spreadsheets.
> The format used to represent formulas is intentionally similar
> to those used by traditional programming languages and
> various spreadsheet programs, using "*" for multiply and
> "/" for divide.
> 
> While formulas can be displayed, they are not designed to
> represent arbitrary mathematical constructs; for that,
> use Mathematical Content instead.
> 
> A formula MUST either be a number or start with an "=" sign;
> the text following the equal sign will here be called an "expression".
> An expression must have following form:
> 
>  expression :== [optional-negation]
>                 ( Number | String | FunctionCall | CellReference )
>                 [ operation expression ]
> 
>  FunctionCall :== FunctionName "(" [ function-argument
>                                      [ "," function-argument ]* ] ")"
> 
> An operation can be "+" (add), "-" (subtract), "*" (multiply), "/" 
> (divide),
> "^" (exponentiate), or the comparatives "=", "!=", "<>", <", "<=", ">", 
> ">=".
> The usual mathematical precedence rules apply: unary minus,
> exponentiation, multiplying and dividing, adding and subtracting,
> and then finally comparitives;
> within these groupings, the order is left-to-right.
> Parentheses may be used, as long as they are properly nested, and they
> override the precedence rules given here.
> 
> A String is surrounded by double-quotes (") or single-quotes (').
> 
> Note that cell references and function calls
> are unambiguous because function calls end in "(" after the name,
> while cell references do not.  However, while cell references
> SHOULD be written so that they begin with a table name, or
> an initial "." if it's the current table, readers SHOULD
> handle formulas where the cell name of the current table omits
> the leading dot (e.g., "=A1+B1", which should be "=.A1+.B1").
> 
> Most systems supporting formulas will support a large number
> of built-in functions.  The following are functions that are
> generally supported (and thus should be interoperable); implementations 
> SHOULD
> implement at least these functions:
> 
> {SUM, etc.  Pick a nice big list, so that interoperability
> is enhanced.  Create the list based on
> OpenOffice and/or Gnumeric or whoever.  The list is going
> to be large, and will need many subsections itself,
> so this will probably need its own subsection
> or section.  But it should be easy to create; a vast number
> of functions are accepted by all, many starting from the
> original VisiCalc.  And although there are many functions,
> most are very easy to define. }
> 
> 
> --- David A. Wheeler
> 


-- 
Michael Brauer                                Phone:  +49 40 23646 500
Technical Architect Software Engineering      Fax:    +49 40 23646 550
StarOffice Development
Sun Microsystems GmbH
Sachsenfeld 4
D-20097 Hamburg, Germany                e-mail: michael.brauer@sun.com