Bug Hunting Session
Bug 80732 - MOD function may provide false result when first argument is calculated
Summary: MOD function may provide false result when first argument is calculated
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.6.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-30 20:32 UTC by Jean-Baptiste Faure
Modified: 2014-07-01 05:19 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
some tests on MOD() function (40.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-30 20:32 UTC, Jean-Baptiste Faure
Details
augmented version of test spreadsheet (170.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-01 04:49 UTC, Jean-Baptiste Faure
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jean-Baptiste Faure 2014-06-30 20:32:58 UTC
Created attachment 102032 [details]
some tests on MOD() function

I do not want reopen bug 50299 but testing that bug I made some error when typing and tried the following:
MOD(4.1*100;10)
MOD(5.1*100;10)
Both give 10 as result instead of 0. We have the same result for the range 64.1 to 81.1. All other values I tested (0.1, 1.1, 2.1, ..., 15.1) give 0 or a very small value which is OK for me (yes, I know what rounding errors are).

I do not know how MOD() function is computed but if the root cause is the same as in bug 50299, something should be done to better control rounding errors in this particular case.

NB: the function QUOTIENT() gives the correct result in all corresponding cases.

Tested with LO 4.3.0.1.0+, 4.2.6.0+, 4.1.6 and 4.0.6 on Ubuntu 14.04 x86-64

Best regards. JBF
Comment 1 Markus Mohrhard 2014-06-30 23:25:04 UTC
Closing with the same argumentation as the other bug. It is a floating point limitationa nd has nothing to do with Libreoffice.
Comment 2 Jean-Baptiste Faure 2014-07-01 04:49:42 UTC
Created attachment 102047 [details]
augmented version of test spreadsheet

I agree that the current behavior is due to a limitation of the floating point calculations on the current computer technology. That said it is possible to workaround the problem in the case of MOD() function by changing its algorithm. For example by computing MOD(X;Y) = X-ROUND(X/Y)*Y
Using INT(X/Y) instead of ROUND(X/Y) gives the same problem as the current algorithm because in some cases the quotient X/Y is inferior by one unit to the correct value due to the fact INT(A) make a truncation to the greatest integer less than A instead of a rounding to the nearest integer as ROUND() does.

There is the same problem with the QUOTIENT() function. Computing it by QUOTIENT(X;Y) = ROUND(X/Y) gives the correct results when computing it by INT(X/Y) gives the same errors.

Please have a look at the second sheet of the new version of the attachment. In column F you can check that the computed values are always near zero: select the column and verify that the sum in the status bar is small.

Best regards. JBF
Comment 3 Jean-Baptiste Faure 2014-07-01 05:19:13 UTC
Argh! my proposition does not work on the serie 0.09, 0.19, 0.29, etc. instead of 0.1, 0.2, 0.3, etc.. It gives a modulo with value equal to -1 :-(
Sorry for the noise. Closing again.

Best regards. JBF