You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@openoffice.apache.org by bu...@apache.org on 2016/02/08 12:06:44 UTC
[Issue 126830] New: Modulo function does not work properly, when
dividend is result of calculations.
https://bz.apache.org/ooo/show_bug.cgi?id=126830
Issue ID: 126830
Issue Type: DEFECT
Summary: Modulo function does not work properly, when dividend
is result of calculations.
Product: Calc
Version: 4.1.1
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: Normal
Priority: P5 (lowest)
Component: ui
Assignee: issues@openoffice.apache.org
Reporter: madtroll@gmail.com
Created attachment 85277
--> https://bz.apache.org/ooo/attachment.cgi?id=85277&action=edit
Bug example
When i was trying to do some manipulations with time as numbers, modulo
function was giving me incorrect result. Based on attached example it seems
like it is related to calculations used to create dividend, not it value.
What I did is I subtracted 9:45 from 15:15, and from result i subtracted 0:30.
Now total time i multiplied by 1440 to get number of minutes. Which is in that
case 300.
Calculated modulo 60 of returned value, gives me me 60 while i'm expecting 0.
When i tried to use 300 as entered value, or i didn't use second subtraction to
obtain 300, then I'm getting expected result.
Other thing is, function if returns true while comparing entered 300 with
calculated one which gives incorrect result.
--
You are receiving this mail because:
You are the assignee for the issue.
[Issue 126830] Modulo function does not work properly, when dividend
is result of calculations.
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126830
bmarcelly <ma...@club-internet.fr> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |marcelly@club-internet.fr
--- Comment #1 from bmarcelly <ma...@club-internet.fr> ---
Created attachment 85285
--> https://bz.apache.org/ooo/attachment.cgi?id=85285&action=edit
Modified example with all decimals
This is the classical case showing that floating point data do not have
infinite precision.
Numbers in Calc are internally coded in a 8-bytes format that can produce
around 16 significant digits.
See https://en.wikipedia.org/wiki/Double-precision_floating-point_format
Time 14:00:00 is internally coded as
14/24 = 0.58333333333333333333333333333333333333333333 ad infinitum.
This value cannot be represented with perfect accuracy.
When working with floating points you must take account of this inevitable
inaccuracy.
See https://en.wikipedia.org/wiki/Floating_point#Accuracy_problems
In the attached spreadsheet I have used a cell format showing 20 decimals (more
than the maximum precision). You can see that H4 and H5 are both incorrect, due
to limited precision. G4 and G5 are displayed identical, but internally they
are not exactly 300.
Computing a modulo on numbers other than integers is a nonsense (although Calc
accepts it). In cells G14 and G15 I have rounded G4 and G5 to the nearest
integer. And then the modulo is correct.
_NOT A BUG_
--
You are receiving this mail because:
You are the assignee for the issue.
[Issue 126830] Modulo function does not work properly, when dividend
is result of calculations.
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126830
orcmid <or...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |orcmid@apache.org
Latest|--- |4.1.2
Confirmation in| |
Ever confirmed|0 |1
Status|UNCONFIRMED |CONFIRMED
Component|ui |help
--- Comment #2 from orcmid <or...@apache.org> ---
RECOMMENDATION
Realize that fractional days are carried as rational, approximate values and
that approximation errors will occur on arithmetic with them, especially when
whole values are expected when converted to integer seconds, minutes, hours,
etc.
I am treating this issue as Confirmed, but changing it to a documentation
issue. We could do better than this, especially since it is very difficult for
an user to see the source of the discrepancy.
CORRECTIONS
When integers are expected, mathematically, use the INT function to ensure that
in the calculation.
Changing the formula =cell*1440 to =INT(cell*1440) to ensure that an integer is
produced works perfectly in the current case.
SUPPLEMENTAL ANALYSIS
In order to confirm exactly that the original problem is with floating-point
accuracy limitation combined with rounding done in various places I did the
following to the original Bug example.
With formulas, such as B8 =B4, B9 = B5, etc., all across to column H,
I then changed the values in rows 8 and 9 to be in Scientific notation, showing
16 digits to the right of the decimal point.
However, this did not show any inaccuracy in the values of in column G although
one sees the conversion limitations in columns B-F. The value in H9 is still
6E01 although H8 is not really 0, it is about 5.68434E-14 and displayed as 0 in
H4.
Instead of looking in the ODF code of the save .ods file, I came up with the
following fix.
I set I4 = FLOOR(H4;1). The formal definition of this result is the largest
integer that does not exceed the given argument.
Using the same for I5, I8, and I9, I get 0, 59, 0, and 59 as the four results.
That is, the value in G5 is just slightly below 300, so mod(G5;60) is a value
just below 60, but it rounds to 60 in H5 but its floor is 59 in I5.
You can check for yourself that =FLOOR(60;1) is indeed 60.
I agree this is not a bug, but recognition of the reason is exacerbated by
roundings that occur in presenting results in ways where the difficult cannot
be seen. In some sense, there is a defect, but how to address it remains a
problem.
I opened up Excel 2016 and created exactly the same two rows. In Excel, IH5 is
also 60 and I5 is also 59.
However, doing the same changes to scientific notation, H8 is revealed to be
5.9999999999999900E+01 although no discrepancy in 3.000...00E+02 is seen in the
value of G8.
While one could worry about this tiny discrepancy in the conversion for a cell
output, and treat this as a confirmed defect. But it is not possible to
eliminate these kinds of discrepancies.
--
You are receiving this mail because:
You are the assignee for the issue.
[Issue 126830] Modulo function does not work properly, when dividend
is result of calculations.
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126830
madtroll@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Hardware|All |PC
OS|All |Windows 7
--
You are receiving this mail because:
You are the assignee for the issue.
[Issue 126830] Modulo function does not work properly, when dividend
is result of calculations.
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126830
madtroll@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |madtroll@gmail.com
--
You are receiving this mail because:
You are the assignee for the issue.
[Issue 126830] Modulo function does not work properly, when dividend
is result of calculations.
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126830
Regina Henschel <rb...@t-online.de> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |rb.henschel@t-online.de
--
You are receiving this mail because:
You are the assignee for the issue.