Greetings!
Another comment from Arne in my inbox this evening. Jira is acting up
and I could not add it to the existing issue.
***************
the OpenFormula specification OpenDocument-v1.4-os-part4-formula.pdf
appears to lack a mechanism which provides the possible precision of
mathematical calculations or the so-called machine epsilon of an
evaluator with reasonable effort and reliability.
*** Background information and rationale
Some existing functions test the equality of values, e.g. 6.4.7 Infix
Operator "=". 6.4.7 contains a paragraph about lack of accuracy, but
users have different requirements for accumulated rounding errors and
other issues in lengthy calculations with floating-point numbers.
The specification should provide a simple way to let users find out the
actual value of the machine epsilon, so that comparisons and decisions
can depend on various simple tests related to epsilon. For example,
users may want the absolute difference of two numbers to be less than 10
* epsilon for crucial calculations (e.g. iterations in approximation
loop formulas); below 1000 * epsilon in less crucial usage; and simply
less than 0.1 in lax usage. Approximation loops become arbitrarily
flexible when the function LAMBDA or a similar mechanism is introduced
in OpenFormula.
The actual epsilon value of an evaluator implementation can depend on a
compile-time configuration option of the evaluator which e.g. allows C
data types "double" and "long double". Formula authors don't yet have a
simple, reliable way to find out the machine epsilon. One indicator
could be the name of the evaluator program (if provided with 6.13.13
INFO), but this is not reliable.
It seems that evaluator implementations typically use the
double-precision floating-point format, a data type which is simply
called "double" in C. But one main criticism of spreadsheet programs is
that they lack sufficient precision for many interesting uses. One
solution in a current, not yet published evaluator project is to provide
a compile-time configuration option which lets users choose between the
C types "double" and "long double" (and the smaller type "float" for
decades-old computers which need slow co-processors or software
emulation for floating-point calculations). The size of the type
"double" seems to be fixed at 64 bits, but the size of a "long double"
type varies among processor platforms and C compilers, e.g. 80 bits or
128 bits. Other types and sizes exist, e.g. octuple precision with 256
bits.
Some formula authors use manual constants like 2^-52 or 2^-1022 for an
epsilon value resp. the smallest positive normal number, but these
numbers only apply to the type "double" and are wrong for other types.
The value of the machine epsilon for an implementation is readily
available to implementors in several programming languages. For example,
C provides the standard identifiers FLT_EPSILON, DBL_EPSILON and
LDBL_EPSILON.
Access to the machine epsilon in formulas could be specified as a
separate function EPSILON() like the existing functions PI() and TRUE().
It would also fit nicely in 6.13.13 INFO with a Category string like
"epsilon" or "eps"; the latter seems appropriate because other Category
strings are also abbreviated.
One problem could be how to provide an epsilon in arbitrary-precision
implementations. A quick Internet search suggested that such
implementations are only available as plug-ins which provide special
functions. Formula authors who use these special functions are probably
aware of the arbitrary precision and don't want an epsilon for these.
Even if a spreadsheet implementation used arbitrary precision
throughout, a reasonable default epsilon or a user-configured epsilon
value could be returned.
An indirect alternative (or addition) could be a function which returns
the size (number of bytes) of the floating-point data type used by an
implementation. Formulas could then use IF expressions, e.g. to use more
iterations of an approximation loop when the type is larger. The number
of bytes is readily available in C and some other programming languages,
e.g. with the expression sizeof(long double).
For a complete solution, one could add the Category strings "tinyflt"
and "tinyexp" to 6.13.13 INFO, providing the smallest positive normal
number and the respective exponent. For the type "double", these values
could be 2^-1022 resp. -1022.
The addition of all these features to an evaluator implementation in
C/C++ only requires a few source code lines, increases the size of a
binary program file only by about one hundred bytes and costs nearly no
processor time.
*** Proposal
Considering the above rationale, please add at least one of these three
mechanisms to the OpenFormula specification:
- The new function EPSILON() has no parameters and returns a value of
type Number: the machine epsilon of the running evaluator
implementation; specifically, the smallest positive floating-point
number for which 1 + epsilon > 1.
- Calling the existing function 6.13.13 INFO with the new Category
string "eps" returns a value of type Number: the value detailed for
EPSILON() above.
- Calling the existing function 6.13.13 INFO with the new Category
string "fltsize" returns a value of type Integer: the size (number of
bytes) of the floating-point data type used by the running evaluator
implementation.
Thank you,
Arne Thomaßen
***********
I hope everyone is at the start of a great week!
Patrick