https://bz.apache.org/ooo/show_bug.cgi?id=127640
--- Comment #3 from ChasVA <[email protected]> --- (In reply to Patricia Shanahan from comment #2) > Created attachment 86302 [details] > Program for analyzing E13 calculation in the prior attachment. > > This is a Java program that prints some useful numbers for understanding the > details of the E13 calculation. It uses BigDecimal, a fixed point decimal > number class, for exact decimal printing and arithmetic. (In reply to Patricia Shanahan from comment #1) > 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. Thank you for your analysis and testing. You've far exceeded my skill set and I appreciate your providing a deeper look into the inner workings of Calc. I'm well aware that the issue stems from using Binary numbers for the calculations; I'm also aware that the workaround is to use the ROUND function to provide accurate numbers for both display and calculation. I disagree with your recommendation about reverse engineering the Excel solution. Apparently Excel is somehow treating the rounding differently. Whether employing a reverse engineering method, or just tweaking the calculation method, OO should handle numbers with two decimal places with greater accuracy. Likely the most common form of spreadsheet in use employs calculations on numbers to two decimal places; spreadsheets tracking currency transaction surely make up a large, if not the largest, subset of spreadsheets. For a spreadsheet using only numbers with two decimal places, it shouldn't be necessary to use the ROUND function as a workaround, nor should "Precision as shown" need to be selected. Had I not set up the conditional formatting as I did, I would never have known that Calc was seeing numbers that appear to be identical on screen as being unequal. Pity the user who doesn't know to use the ROUND function and bases other calculations on comparisons of numbers that appear to be equal but aren't. I've been using spreadsheets in one form or another since the 1989s, starting with CalcStar, which was bundled with WordStar by MicroPro. I've used Excel extensively in the past, and have also occasionally used SoftMaker's FreeOffice. I've never encountered this error before in any other program. So there's obviously a different way to handle the vagaries of the results of calculating in binary. I wish I knew more about coding so that I could propose some sort of patch. Absent an internal fix, perhaps "Precision as shown" should be selected as the default when installing Calc? Thanks again for all your work. -- You are receiving this mail because: You are the assignee for the issue.
