Issue 69749 - Consolidate precision of binary representation and decimal rounding
Consolidate precision of binary representation and decimal rounding
 Status: ACCEPTED None Calc Application code (show other issues) OOo 2.0.2 All All P3 Trivial with 2 votes (vote) --- AOO issues mailing list (view as issue list)

 Reported: 2006-09-21 19:46 UTC by bdillow 2017-05-20 11:01 UTC (History) 9 users (show) b.overgaauw b_art issues jbf.faure kennybobs kpalagin luca.boschini mail tonictrinker ENHANCEMENT --- ---

Attachments
Trunc function bug seen in sheets 1 and 2 (277.50 KB, application/vnd.ms-excel)
2006-09-21 19:49 UTC, bdillow
no flags Details
test case with string conversion involved from issue 74606 (10.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-02-19 14:03 UTC, ooo
no flags Details
nice test case from issue 18154 (11.70 KB, application/vnd.sun.xml.calc)
2007-11-13 12:06 UTC, ooo
no flags Details

 Note You need to log in before you can comment on or make changes to this issue.
 bdillow 2006-09-21 19:46:41 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.``` bdillow 2006-09-21 19:49:14 UTC ```Created attachment 39293 [details] Trunc function bug seen in sheets 1 and 2``` frank 2006-09-22 08:33:30 UTC ```Hi Eike, one for you ? Frank``` ooo 2006-09-25 16:21:22 UTC ```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".``` ooo 2006-09-28 13:58:40 UTC `*** Issue 69798 has been marked as a duplicate of this issue. ***` ooo 2006-09-28 14:01:46 UTC ```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``` ooo 2007-02-06 11:03:40 UTC `*** Issue 74209 has been marked as a duplicate of this issue. ***` ooo 2007-02-06 11:05:27 UTC ```From issue 74209: =(-943.59+950)=6.41 gives FALSE``` ooo 2007-02-19 14:00:58 UTC `*** Issue 74606 has been marked as a duplicate of this issue. ***` ooo 2007-02-19 14:03:43 UTC ```Created attachment 43166 [details] test case with string conversion involved from issue 74606``` frank 2007-02-26 16:27:16 UTC `*** Issue 74832 has been marked as a duplicate of this issue. ***` ooo 2007-08-24 21:03:25 UTC `*** Issue 78280 has been marked as a duplicate of this issue. ***` ooo 2007-08-24 21:05:58 UTC ```Test case from issue 78280: Fill Series from 0.7 and 0.72 increments by 0,199999999999999 ``` frank 2007-10-29 12:21:08 UTC `*** Issue 83027 has been marked as a duplicate of this issue. ***` ooo 2007-10-29 12:45:56 UTC `*** Issue 83073 has been marked as a duplicate of this issue. ***` ooo 2007-11-13 11:30:31 UTC `*** Issue 18154 has been marked as a duplicate of this issue. ***` ooo 2007-11-13 12:06:18 UTC ```Created attachment 49622 [details] nice test case from issue 18154``` jbf.faure 2008-01-08 20:01:09 UTC `Add me to CC.` Regina Henschel 2008-04-10 15:42:03 UTC `*** Issue 88119 has been marked as a duplicate of this issue. ***` kla 2008-09-29 11:37:49 UTC `*** Issue 94416 has been marked as a duplicate of this issue. ***` Regina Henschel 2009-03-28 13:56:21 UTC `*** Issue 100439 has been marked as a duplicate of this issue. ***` Regina Henschel 2009-03-28 13:59:56 UTC `*** Issue 88429 has been marked as a duplicate of this issue. ***` Regina Henschel 2009-03-28 14:03:25 UTC `*** Issue 72967 has been marked as a duplicate of this issue. ***` Regina Henschel 2009-03-30 19:31:39 UTC `*** Issue 100654 has been marked as a duplicate of this issue. ***` Regina Henschel 2009-07-09 18:39:10 UTC `*** Issue 103445 has been marked as a duplicate of this issue. ***` Regina Henschel 2009-08-01 17:28:36 UTC `*** Issue 84843 has been marked as a duplicate of this issue. ***` b_art 2010-02-01 08:48:36 UTC ```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.``` Regina Henschel 2011-07-05 18:38:45 UTC `*** Issue 118244 has been marked as a duplicate of this issue. ***` Regina Henschel 2013-12-01 15:35:56 UTC `*** Issue 123784 has been marked as a duplicate of this issue. ***` Regina Henschel 2014-07-09 21:53:26 UTC `*** Issue 125229 has been marked as a duplicate of this issue. ***` Regina Henschel 2014-12-29 12:42:31 UTC `*** Issue 125978 has been marked as a duplicate of this issue. ***` Regina Henschel 2016-06-06 18:50:17 UTC `*** Issue 126992 has been marked as a duplicate of this issue. ***` Marcus 2017-05-20 11:01:00 UTC `Reset assigne to the default "issues@openoffice.apache.org".`