Greetings!
Another batch of comments from Arne, which I am posting to:
https://issues.oasis-open.org/browse/OFFICE-4213I did notice his suggestion for test cases. We need to discuss an
invitation by the TC for him to contribute such tests as a separate work
product, or perhaps even an open source project.
I hope everyone is having a great week!
Patrick
PS: I found a quote in Latin to describe requests for tests but not
writing them: "Multi testicula postulant, pauci scribunt."
"Many demand testicles, few write them."
In Latin, testiculus is the diminutive of testis (witness), meaning
"little witness" or "testicle."
-------- Original Message --------
Subject: ODF 1.4 recalculated formula spec suggestions, batch 7
Date: 2026-07-05 17:38
From: "arne@arne-thomassen.de" <
arne@arne-thomassen.de>
To: "patrick@durusau.net" <
patrick@durusau.net>
Hi,
the OpenFormula specification OpenDocument-v1.4-os-part4-formula.pdf
appears to have some flaws. I'd like to suggest the following changes to
individual functions and topics in addition to the batches from my
e-mails on 19 January, 15 February, 9 March, 31 March, 25 April and 22
May 2026:
- 2.3 Evaluator Conformance: please provide explicit test cases in a
simple machine-readable format. I found "Test Cases" listed within an
old OpenFormula specification document from 2010, but these seem to have
been removed in newer specification versions. - The current section 2.3
provides several "groups" for evaluators: small, medium, large; and
"Basic Limits"; and certain syntax parts, type conversions, operators
and functions must be implemented. But the section only states that
implementations "shall implement" the functions, that is, the actual
results of the calculations don't matter for conformance. The required
results are quite clear e.g. for usual arithmetic operators and for
functions for which the specification provides explicit mathematical
formulas. But for many functions, the specification only provides vague
prose, allowing wide ranges of return values to be considered
sufficient. - For each non-trivial function, the specification should
provide e.g. five test cases for usual parameter values and some test
cases for unusual parameter value combinations (corner cases). Each test
case should consist of an expression and the required evaluation result.
Implementors can then "advertise" which percentage of the maximum test
score was achieved by the implementations. Test cases should be grouped
according to the small, medium and large groups. - Additional tests
should assess the precision; e.g. a score 10 for an exact match and
fewer points depending on the order of magnitude of the deviation. At
least the precision of Newton's method (for functions like 6.12.24 IRR)
and of summations (e.g. for 6.18.3 AVERAGE and 6.18.63 PROB) and of
mathematical special functions (e.g. 6.16.12 BESSELI) should be tested
this way. The summation tests should contain cases which can only
succeed with Kahan summation or even higher orders such as Kahan-Klein
summation. - All test cases should be in one plain-text file which is
easily machine-readable, e.g. one test case per line. Expression and
solution should be separated by a character sequence which isn't used
anywhere else in OpenFormula; example: ABS(-1) -> 1
- 5 Expression Syntax: the Logical constants FALSE and TRUE are not
specified. (Near the end of 5.11 Named Expressions, they are only
mentioned in passing.) Please specify them, maybe directly after 5.3
Constant Numbers. - While at it, please specify the special Number
constant value INFINITY and maybe also NAN. The specification mentions
infinity in many places, e.g. as an implicit rounding direction, but
formula authors cannot yet indicate it explicitly. Many mathematical
functions could reasonably accept infinity as a parameter or return it,
e.g. EXP(-INFINITY) = 0, LN(+0.0) = -INFINITY, LN(-0.0) = NAN;
cumulative distributions could return 1 for the parameter +INFINITY and
0 for -INFINITY. These behaviors should be disabled by default for
backward compatibility, but users should be able to enable them easily
with new switches like ACCEPT-INFINITY and RETURN-INFINITY in 3.4
Host-Defined Behaviors. Special new functions can have it enabled
unconditionally; e.g. a function "NEXTTOWARD(Number From, Number To):
Number" could return the nearest available floating-point number "toward
+INFINITY" or "toward -INFINITY".
(<https: en.cppreference.com c numeric math nexttoward>) - One
"inspiration" for this proposal are many function implementations in
Gnumeric which can work with infinite values, at least internally.
- 5.5 Operators, "Table 1 - Operators": the table row for the string
concatenation operator "&" contains this text: "Note that unary (prefix)
+ and - have a different precedence." That doesn't make sense for string
concatenation and looks like it was accidentally copied from the row
about addition and subtraction directly above it. Please remove the
quoted text for "&". - Table 1 lacks information about the operator "!!"
from 5.10.6 Automatic Intersection. This operator should probably be put
into one of the top rows.
6.3.5 Conversion to Number: please specify or at least recommend how
complex numbers (type Complex) should be converted to the type Number.
In C, two obvious choices are the functions creal(), which simply
discards the imaginary part of the complex number; and cabs(), which
returns the length of the vector in the complex plane. When the input is
a real number (imaginary part 0), creal() and cabs() return the same
result.
- 6.12 Financial Functions: several functions provide an optional
parameter like this: Number PayType = 0. Please specify a new data type
PaymentType which is a sub-type of Integer and only accepts the values 0
("due at the end") and 1 ("due at the beginning"). The separate tables
and the lengthy prose in these function specifications can be
consolidated into one table in a new section near 4.11.7 Basis. The
function parameter identifiers are currently "Type" in some cases and
"PayType" in others; this should be unified. (This proposal for
PaymentType is somewhat related to
<https: issues.oasis-open.org browse office-3915>.) - This could be a
part of a larger cleanup of parameters and their descriptions. Many
descriptions are repeated frequently and sometimes with separate tables,
e.g. the tables for payment frequencies in 6.12. One general problem
with repetitions is that readers have to inspect whether it is an exact
repetition or it has important modifications which might be easily
overlooked. Please replace the repetitions with simple references to the
consolidated descriptions, which can e.g. be put near the beginning of
6.12. - One minor clean-up is to unify the mixed-case parameter
spellings "Fv" and "Pv" for some financial functions to the more usual
all-caps spellings "FV" and "PV".
- 6.12.12 CUMPRINC: the constraint Value > 0 is missing (at least in the
PDF version); cf. the constraint in 6.12.11 CUMIPMT and the meaning of
these functions.
- 6.12.13 DB: a constraint like Salvage < Cost is missing. - The given
constraint Period > 0 seems to be insufficient because Period is
declared as Number. The correct solution seems to be to use the type
Integer; otherwise, the constraint should probably be ">= 1" instead of
"> 0". - The paragraph "Semantics" says this about the formal parameter
Month: "If a value is specified for Month, LifeTime and Period are
assumed to be measured in years." What should happen if no value is
specified for Month? The words "is specified" seems to apply whenever an
explicit Month value is written into the actual parameter list. But what
if that explicit value happens to be the default value 12? The words
"assumed to be measured in years" seem to be irrelevant and should be
replaced with a hint like this: "LifeTime and Period must use the same
unit of measurement, e.g. years. The parameter Month represents twelfths
of that unit of measurement." - For clarity, please rename the parameter
Month to the plural Months or to a neutral notion like Twelfths.
- 6.12.38 PRICE: the paragraph "Semantics" says: "C is the number of
coupons between Settlement date and Redemption date". The word
"Redemption" is stylized like a formal parameter identifier (at least in
the PDF version); but the parameter Redemption represents monetary
amounts, not dates. Should the notion "Redemption date" be replaced with
"maturity date" or something else?
- 6.12.40 PRICEMAT: the constraint Issue < Settlement seems to be
missing.
- 6.18.22 FDIST, 6.18.23 LEGACY.FDIST, 6.18.24 FINV, 6.18.25
LEGACY.FINV: the formal data type of the parameters R1 and R2 is Number,
but should be Integer; as mentioned in the paragraph "Constraints".
- 6.18.29 FREQUENCY: the data type of the formal parameter Bins is given
as NumberSequenceList, but the paragraph "Constraints" requires that
Bins "shall be a column vector", but this in turn is contradicted by
lengthy descriptions in the paragraph "Semantics". Please clear up these
contradictions. (The quoted "column vector" wording should probably be
removed. The type NumberSequenceList seems to be a good choice.)
- 6.18.77 LEGACY.TDIST: the paragraph "Semantics" provides an integral
over a specified function f(t). The sources I found use that function
f(t), but without integral. Please check. (One of the mentioned sources:
<https: real-statistics.com students-t-distribution t-distribution-basic-concepts>)
- 6.18.78 TINV: the parameter identifier "DegreeOfFreedom" should use
the plural "Degrees" for consistency.
- 6.19.2 ARABIC: the return type should be Integer instead of Number;
all returned values are integers.
- It would be nice if formula authors could set the desired precision of
approximations for functions using the Newton-Raphson method and some
other functions explicitly, e.g. for Bessel functions. For example, low
precision might usually be good enough for a user, but high precision
might be necessary for a few formulas. The setting function should
enable formula authors to balance faster calculation and higher
precision, depending on the formula authors' needs. Please consider
adding a new function declared like this: WITH.PRECISION(Any X; [Number
Precision]; [Integer Summation]; [Number Tolerance]; [Integer Flags]):
Any. The parameters Precision, Summation, Tolerance and Flags are
optional; if a parameter isn't provided in the function call, the
respective setting is left unchanged. If one of these values equals -1,
the function resets to its built-in value for (possibly nested)
computations. This function sets the desired minimum precision for all
(possibly nested) computations of X to the given non-negative Precision
value, e.g. 1e-20. - If a formula which itself doesn't demand a
precision is referenced by several formulas with different precision
demands, an implementation could simply cache the result from the
strictest demand and return that to all callers. - The parameter
Summation indicates how much the implementation should care about
floating-point rounding errors in summation algorithms. The value 0
indicates the simplest summation without caring about rounding. The
value 1 indicates the basic Kahan summation. Higher numbers indicate
increasingly sophisticated Kahan-Klein management orders of rounding
errors. - The parameter Tolerance indicates e.g. below which positive
number a Gauss-Jordan elimination algorithm fails or a matrix should be
considered degenerate and not invertible. (Mathematically, a matrix is
only degenerate if e.g. the highest diagonal absolute value is exactly
0. Some tolerance is reasonable because of rounding errors with
floating-point numbers.) This is important in calculations which involve
LR (LU) decomposition of matrices. - The parameter Flags is a "bit-wise
or" combination of values which e.g. indicate that certain calculation
parts should be performed in separate "passes" for increased precision
and numerical stability. For example: 1 indicates that an average value
shall be calculated separately (and not "online" during calculation of a
standard deviation); 2 indicates that a standard deviation should be
calculated in a separate pass (e.g. during the calculation of skewness
or kurtosis). (Background information:
<https: en.wikipedia.org wiki algorithms_for_calculating_variance>) -
Please specify the related function PRECISION(Integer Kind=0): Number.
Depending on the parameter Kind, this returns the currently active
precision (0), summation (1), tolerance (2) or flags (3) setting;
outside of calls to WITH.PRECISION (or e.g. if the respective parameter
was -1), it returns the default precision of the implementation or an
implementation-wide user-configured value or e.g. a workbook-specific
setting.
- Many sentences throughout the specification from "5.7 Nonstandard
Function Names" onward contain a word combination of the form "examples
include" instead of "examples are", e.g. "Examples of such names
include". Please remove the redundancy. (Both words "examples" and
"include" imply that an incomplete enumeration follows.)
Please consider these additional suggestions.
Thank you,
Arne Thomaßen</https:></https:></https:></https:></
patrick@durusau.net></
arne@arne-thomassen.de>