Issue 15559

For additional discussion on this, please see the thread:

"IEEE Floating Point Representation in Calc" in the Users e-mail list. The URL
for the thread is:

http://www.openoffice.org/servlets/ReadMsg?msgId=710500&listName=users


OOo Calc has implemented code (in 1.1 Beta 2, the code can be found in
sal/inc/rtl/math.hxx) that results in an optimization where the results of
arithmetic operations that are "close to zero" are set to zero.

The result of this approach (which is also used in Excel >= 97), is a deviation
from the results one would expect to get according to the IEEE 754 standard.

An example of this difference across Calc, Excel and Gnumeric is as follows:

Calc 1.1 Beta2
Cell Formula          Value
= 4.145 * 100 + 0.5   4.15000000000000000000E+02
= 0.5 - 0.4 - 0.1     0.00000000000000000000E+00
=(0.5 - 0.4 - 0.1)    0.00000000000000000000E+00


MS Excel 2002 (XP)
Cell Formula          Value
= 4.145 * 100 + 0.5   4.15000000000000000000E+02
= 0.5 - 0.4 - 0.1     0.00000000000000000000E+00
=(0.5 - 0.4 - 0.1)    -2.77555756156289000000E-17
*The third line is a bug in Excel's parsing of the cell formula, which appears
to obviate MS' optimization approach resulting in the "correct" IEEE 754 result.


Gnumeric 1.0.12:
Cell Formula          Value
= 4.145 * 100 + 0.5   +4.14999999999999943157E+02
= 0.5 - 0.4 - 0.1     -2.77555756156289135106E-17
*Gnumeric does not appear to allow the surrounding parens.


Gnumeric's results represent the "expected" values as a result of the IEEE 754
standards inability to exactly represent certain numbers.


Lastly, for the sake of completeness, I include the results of the same
calculatons from R (http://www.r-project.org/), which is a GPL math/stats
application which is very precise about these issues.

> print(4.145 * 100 + 0.5, digits = 20)
[1] 414.99999999999994
> formatC(4.145 * 100 + 0.5, format = "E", digits = 20)
[1] "4.14999999999999943157E+02"

> print(0.5 - 0.4 - 0.1, digits = 20)
[1] -2.775557561562891e-17
> formatC(0.5 - 0.4 - 0.1, format = "E", digits = 20)
[1] "-2.77555756156289135106E-17"


To address these issues, a request for the following:

1. There should be some prominent documentation as to the inclusion of the
aforementioned optimization, including the specifications/tolerances under which
the optimzation will influence results. This will enable users to be aware of
the effect on calculations.

2. An option in the Calculation section of the Spreadsheet preferences to enable
/disable the optimization, therefore allowing a user to secure results in
accordance with the IEEE 754 spec and make independent decisions as to the
format/precision of resultant calculations.

Your consideration for the above is appreciated.  

Best regards,

Marc Schwartz