OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: RE: [office-formula] Formula Processing Model

I happen to believe that we do need a processing model for a number of
reasons.  One of them has to do with interdependent formulas in a
(re-)calculation and the ways that data not provided directly in the formula
itself is acquired.  One might not be able to answer all of the questions,
but a processing model of some sort is needed so one can tell at least what
the questions are and which ones are not being answered (or, preferably. are
explicitly and identifiably implementation-defined/-dependent).

The interesting question is, what is the least one must constrain
OpenFormula with normative language in order to have any prospect of
interoperability without compelling out-of-band agreements among
implementers (including use of a common code base) because the specification
is inadequate on the subject.

 - Dennis


The interdependence with recalculation will arise, in the evaluator, when a
cell or other value source referenced in the first formula is accessed and
that source is determined by a formula.  Clearly, if there is no known
value, there will be a (re-) calculation.  If there is a value that has been
previously calculated, it is tricky whether recalculation should occur or
not.  Clearly recalculation is not required if the result would not be
impacted.  Presumably, recalculation will be done if the "recalculate" flag,
the extra "=", is present.  Or maybe not: If that same formula is indirectly
referenced more than once in evaluation of another formula, how many times
does the recalculation happen?

Unfortunately, we don't have a way of saying "don't recalculate this."  We
only have a way of saying "must recalculate."  There is probably more that
could be said on how even "must recalculate" is handled (as I just
suggested) and what its value(s) is/(are) in these situations. 

There are complex interdependencies.  For example, one can, in expanding a
set of calculations and the calculated results they depend on, arrive at a
dependency on a formula whose recalculation is in progress.  Evaluators have
ways of detecting such things and failing.  It helps that formulas tend to
be free of side effects although there are issues when a formula depends on
the same formula-determined value in more than one way, and when there are
dependencies on external dynamic variables, as when using random numbers,
NOW(), some external data access or user input, etc.

I recall evaluators that will iterate some number of times seeking a stable
result.  (I haven't checked lately to see if current products have such
features and whether they are user controllable.)

There is also interaction with processor setting on whether recalculation is
done every time a formula is entered or a value that a formula depends on is
altered.  Delayed recalculation can be more useful when recalculation is
costly, but trouble-shooting and failure detection becomes far more complex,
as is the evaluator determining an order to perform the recalculations in so
everything works out and unbearable repetition of the same calculations is

I'm not sure many cyclic-dependency failures ever show up in produced
formulas these days.  When there is immediate calculation on entry or
alteration of a formula in my simple-spreadsheet work, a cyclic dependency
usually causes an interruption that an user must act on and it is not clear
the offending formula (that is, the one that reveals the problem) is even
allowed into the spreadsheet if the user fails to act and if failing to act
is even an option.  Note that if formula evaluations are not immediately
attempted or their dependencies assessed when entered by an user, this
detection can be delayed until a recalculate point and be awful for an user
to then figure out.  

There are more cases, and I am not sure it is always possible to prevent
cycles and other inter-formula dependency cases (for example, if a cell
reference is determined dynamically via functions that effectively calculate
references or indexes).

Probably the worst case of delayed recalculation is on "save," especially
when the user or the operating system is attempting to save and close the
spreadsheet in a dangerous situation (imminent power failure is my nightmare
case, something that plagues both laptop and desktop workers).  I'm not sure
whether the persisted document (assuming work is not lost) can be assured to
be in a clean, "conformant" state under such conditions.

-----Original Message-----
From: Patrick Durusau [mailto:patrick@durusau.net] 
Sent: Friday, January 22, 2010 04:39
To: office-formula@lists.oasis-open.org
Subject: Re: [office-formula] Formula Processing Model


Eike Rathke wrote:
> Hi Patrick,
>> So, the lead in paragraph under General:
>> "OpenFormula defines the semantics of expressions, when recalcalculation

>> occurs, and limits on formulas. It does not specify any particular  
>> process for evaluations consistent with those definitions."
> Actually we do not define when recalculation occurs, with the one
> exception of formulas tagged for forced recalculation. It is up to the
> implementation to decide when a formula or a part thereof is to be
> recalculated.
I was re-casting:

"This section describes the basic formula processing model: how 
expressions are calculated, when recalculation occurs, and limits on 

But after re-reading the section on recalculation, it is just advisory 
and not normative. Yes?

Do we need to reduce and keep as a note, perhaps with formulas tagged 
for forced recalculation?

The opening line then becomes:

"OpenFormula defines the semantics of expressions and limits on those 
expressions. It does not specify any particular process for evaluations 
consistent with those definitions."

Thinking that saying "formulas" isn't any different from "expression." Yes?

Hope you are looking forward to a great weekend!


Patrick Durusau
Chair, V1 - US TAG to JTC 1/SC 34
Convener, JTC 1/SC 34/WG 3 (Topic Maps)
Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)

To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  Follow this link to all your TCs in OASIS at:

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]