Apache OpenOffice (AOO) Bugzilla – Issue 69749
Consolidate precision of binary representation and decimal rounding
Last modified: 2017-05-20 11:01:00 UTC
I encountered this problem making a spreadsheet to convert decimal angles to angles in degrees, minutes, and seconds. If trunc() is passed a value that must be calculated, and the answer passed to trunc() is a multiple of 6, trunc() returns one less than the correct answer. Example: Cell Formula Data Displayed Correct Answer ------ -------- -------------- -------------- A1 - 22.2 - B1 =trunc(A1) 22 same C1 =trunc((A1-B1)*60) 11 12 D1 =(((A1-B1)*60)-C1)*60 60 0 22.2 degrees is 22 degrees, 12 minutes, 0 seconds Cell Formula Data Displayed Correct Answer ------ -------- -------------- -------------- A2 - 12.1 - B2 =trunc(A2) 12 same C2 =trunc((A2-B2)*60) 5 6 D2 =(((A2-B2)*60)-C2)*60 60 0 12.1 degrees is 12 degrees, 6 minutes, 0 seconds Cell Formula Data Displayed Correct Answer ------ -------- -------------- -------------- A3 - 36.8 - B3 =trunc(A3) 36 same C3 =trunc((A3-B3)*60) 47 48 D3 =(((A3-B3)*60)-C3)*60 60 0 36.8 degrees is 36 degrees, 48 minutes, 0 seconds BUT: Cell Formula Data Displayed Correct Answer ------ -------- -------------- -------------- A4 - 0.2 - B4 =trunc(A4) 0 same C4 =trunc((A4-B4)*60) 12 12 D4 =(((A4-B4)*60)-C4)*60 0 0 0.2 as a starting number works correctly I started the first column at zero, incremented by 0.1 each row and dragged down to 100.0 The following range of values cause trunc() to return incorrect values: 5.1 - 18.8 (not including whole numbers, which give correct answers) 75.3 - 100 (INCLUDING whole numbers) I haven't been able to recreate this bug with any other formulas or uses of trunc(). It has similarly strange reactions to numbers with more decimal places, e.g. 1.05 causes incorrect values as well.
Created attachment 39293 [details] Trunc function bug seen in sheets 1 and 2
Hi Eike, one for you ? Frank
This is nothing specific to the TRUNC function, but is initially caused by the substraction operation, where an exact binary represention of the decimal values is not possible, for example the result of 22.2 - 22 is _not_ 0.2 but 1.999999999999999 instead, so any further calculation with that value propagates a rounding error. Note also that the results in column D most times are not exactly 0 or 60, but for example 0.000000000002557 instead; when loading the document into Excel they do not equal 0 for similar reasons, just that Excel seems to apply a slightly different magic to the simple multiplication by 60 operation so the TRUNC results look like they're correct. For correct calculation a user provided rounding of the interim values to the desired precision is needed, e.g. in cell C1 the formula =TRUNC(ROUND(A1-B1;2)*60) produces the expected result. Taking this issue as a placeholder for "rethink and consolidate rounding issues".
*** Issue 69798 has been marked as a duplicate of this issue. ***
From issue 69798: = 2^48 + 1 - 2^48 results in 0.0 instead of 1.0 Excel does the same, just with 2^50: = 2^50 + 1 - 2^50 results in 0.0 instead of 1.0
*** Issue 74209 has been marked as a duplicate of this issue. ***
From issue 74209: =(-943.59+950)=6.41 gives FALSE
*** Issue 74606 has been marked as a duplicate of this issue. ***
Created attachment 43166 [details] test case with string conversion involved from issue 74606
*** Issue 74832 has been marked as a duplicate of this issue. ***
*** Issue 78280 has been marked as a duplicate of this issue. ***
Test case from issue 78280: Fill Series from 0.7 and 0.72 increments by 0,199999999999999
*** Issue 83027 has been marked as a duplicate of this issue. ***
*** Issue 83073 has been marked as a duplicate of this issue. ***
*** Issue 18154 has been marked as a duplicate of this issue. ***
Created attachment 49622 [details] nice test case from issue 18154
Add me to CC.
*** Issue 88119 has been marked as a duplicate of this issue. ***
*** Issue 94416 has been marked as a duplicate of this issue. ***
*** Issue 100439 has been marked as a duplicate of this issue. ***
*** Issue 88429 has been marked as a duplicate of this issue. ***
*** Issue 72967 has been marked as a duplicate of this issue. ***
*** Issue 100654 has been marked as a duplicate of this issue. ***
*** Issue 103445 has been marked as a duplicate of this issue. ***
*** Issue 84843 has been marked as a duplicate of this issue. ***
In Office 2007: 9007199254740990,00000 =2^53 9007199254740980,00000 =A1-7 (The display(!) is different, but comparing is not?) TRUE =A2=A1 Open Office is wrong till 8 is subtracted. In Open Office: 9007199254740990,00000 =2^53 9007199254740960,00000 =A1-31(The display(!) is different, but comparing is not?) 1,00000 =A2=A1 Open Office is wrong till 32 is subtracted.
*** Issue 118244 has been marked as a duplicate of this issue. ***
*** Issue 123784 has been marked as a duplicate of this issue. ***
*** Issue 125229 has been marked as a duplicate of this issue. ***
*** Issue 125978 has been marked as a duplicate of this issue. ***
*** Issue 126992 has been marked as a duplicate of this issue. ***
Reset assigne to the default "issues@openoffice.apache.org".