Need more detailed definitions for formulas

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

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