Apache OpenOffice (AOO) Bugzilla – Issue 15559
Option to Enable/Disable "Close to Zero" Floating Point Optimization
Last modified: 2013-08-07 15:12:27 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: 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
Hi Bettina, one4you
OpenOffice.org 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: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ 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: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".