Issue 126830 - Modulo function does not work properly, when dividend is result of calculations.
Summary: Modulo function does not work properly, when dividend is result of calculations.
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: help (show other issues)
Version: 4.1.1
Hardware: PC Windows 7
: P5 (lowest) Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-02-08 11:06 UTC by madtroll
Modified: 2016-02-15 00:50 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.2
Developer Difficulty: ---


Attachments
Bug example (9.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-08 11:06 UTC, madtroll
no flags Details
Modified example with all decimals (11.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-14 20:31 UTC, bmarcelly
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description madtroll 2016-02-08 11:06:44 UTC
Created attachment 85277 [details]
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.
Comment 1 bmarcelly 2016-02-14 20:31:37 UTC
Created attachment 85285 [details]
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_
Comment 2 orcmid 2016-02-15 00:50:53 UTC
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.