Issue 15559 - Option to Enable/Disable "Close to Zero" Floating Point Optimization
Summary: Option to Enable/Disable "Close to Zero" Floating Point Optimization
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1 Beta2
Hardware: PC All
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2003-06-12 14:53 UTC by Unknown
Modified: 2013-08-07 15:12 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description Unknown 2003-06-12 14:53:38 UTC
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:

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 (, 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
Comment 1 oc 2003-06-16 08:55:40 UTC
Hi Bettina,
Comment 2 ace_dent 2008-05-16 02:41:26 UTC Issue Tracker - Feedback Request.

The Issue you raised has the status 'New' pending further action, but has not
been updated within the last 4 years. Please consider re-testing with one of the
latest versions of OOo, as the problem(s) may have already been addressed.
Either use the recent stable version:
or consider trying the new OOo 3 BETA (still in testing):
Please report back the outcome so this Issue may be Closed or Progressed as
necessary - otherwise it may be Resolved as Invalid in the future. You may also
wish to search for (and note) any duplicates of this Issue that may have
advanced further by checking the Issue Tracker:
Many thanks,
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~
Comment 3 bettina.haberer 2010-05-21 15:16:34 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements".