[office-formula] Calculation Settings

[Eric Patterson <ericpa@exchange.microsoft.com>]

I've been reviewing Calculation Settings and finding that conformance is a bit ambiguous.

Looking at sections 2.1.1 and 3.3, they indicate that conforming applications shall implement the calculation settings, and that applications may use non-default values for new documents.

What is unclear is whether support of specific values are required for conformance.

For example, table:null-date lists some commonly used values in a note, but list no values as required for conformance.  Are conforming applications any that read and write a single value that conforms to the definition of table:null-date?  Is application conformance reading all of the settings and only loading files with the combinations of settings that are supported by the application?

-Eric



From 2.1.1:
-----------------------
If the formula is embedded in an OpenDocument document, applications conforming to the small group shall implement the following calculation settings (that is, each setting shall have the effect as defined in this specification):
1.      table:case-sensitive
2.      table:precision-as-shown
3.      table:search-criteria-must-apply-to-whole-cell
4.      table:automatic-find-labels
5.      table:use-regular-expressions
6.      table:use-wildcards
7.      table:null-year
8.      table:null-date


3.3     OpenDocument Calculation Settings
-----------------------------------------
If it is in an OpenDocument file, formula recalculation shall be influenced by “calculation settings”, and where they apply they are part of the recalculation context. Several are defined in the OpenDocument specification. Below are relevant calculation settings, their default value (the value if there is no relevant setting at all), and a description of their meaning:
1.      table:case-sensitive (true): if true, text comparisons are case-sensitive. This influences the operators =, <>, <, <=, >, and >=, as well as database query functions that use them. Note that the EXACT function is always case-sensitive, regardless of this calculation setting.
2.      table:precision-as-shown (false): If true, calculations are performed using rounded values of those displayed; otherwise, calculations are performed using the precision of the underlying numeric representation.  Note: This does not impose a particular numeric model.  Since implementations may use binary representations, this rounding may be inexact for decimal value.
3.      table:search-criteria-must-apply-to-whole-cell (true): If true, the specified search criteria shall apply to the entire cell contents if it is a text match using = or <>; if not, only the initial text needs to match.
4.      table:automatic-find-labels (true): if true, row and column labels are automatically found.
5.      table:use-regular-expressions (true): If true, regular expressions are used for character string comparisons and when searching
6.      table:use-wildcards (false): If true, wildcards question mark '?' and asterisk '*' are used for character string comparisons and when searching. Wildcards may be escaped with a tilde '~' character.
7.      table:null-year (1930): This defines how to convert a two-digit year into a four-digit year. All two-digit year values are interpreted as a year that equals or follows this year.
8.      table:null-date (1899-12-30): Defines the beginning of the epoch; a numeric date of 0 equals this date.

TODO: Check that the use-regular-expressions is sufficiently defined in Volume 1.

Applications may choose to set a calculation setting to a non-default value when creating a new document (giving the user the effect of different defaults), but when writing to the OpenDocument format they shall include any overriding value.

Note: Excel 2003 semantics are case-sensitive=false and search-criteria-must-apply-to-whole-cell=false.