https://bz.apache.org/ooo/show_bug.cgi?id=127640
Patricia Shanahan <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |[email protected] --- Comment #1 from Patricia Shanahan <[email protected]> --- Numbers in Apache OpenOffice are represented in IEEE 754 64-bit Binary Floating Point. The key word here is "Binary". All the exactly representable numbers are terminating binary fractions. That includes decimal fractions ending in .00, .25, .50, .75. It excludes all other decimal fractions with only two digits after the decimal point. Other values can be approximated very closely, resulting in rounding error. OpenOffice and Excel do at least three things to hide rounding error: 1. Print non-zero digits that probably represent rounding error as zero. 2. Treat almost equal numbers as equal. 3. Reduce very slightly non-zero results of addition or subtraction to zero. These behaviors affected the example spreadsheet, and make it harder to do detailed analysis. I calculated several numbers used in this comment in a Java program that I will attach to this report. I found by experiment that forcing E13 to be exactly 1853.13 changes the FALSE answers to TRUE. Changing E12 does not affect them, so the problem starts in the calculation of E13. The exact inputs to the E13 subtraction are: 1873.1199999999998908606357872486114501953125 and 19.989999999999998436805981327779591083526611328125 The exact sum of these numbers is: 1853.129999999999892423829805920831859111785888671875 but the closest representable number is: 1853.129999999999881765688769519329071044921875 The closest representable number to 1853.13 is 1853.1300000000001091393642127513885498046875, which rounds away from zero. At this point three rounding errors are all operating in the same direction, towards zero. As input to the subsequent calculations, this results in a larger deviation than OpenOffice suppresses at E37 and E38. I do not recommend trying to reverse engineer the Excel behavior. The knowledge that all column E numbers are the results of addition and subtraction of numbers with two decimal digits after the decimal point is valuable information. I recommend changing the spreadsheet to round the result of the addition and subtraction in the column E calculation to two decimal places. -- You are receiving this mail because: You are the assignee for the issue.
