Bug 80732

Summary: MOD function may provide false result when first argument is calculated
Product: LibreOffice Reporter: Jean-Baptiste Faure <jbfaure>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: RESOLVED NOTABUG    
Severity: normal CC: newbie-02
Priority: medium    
Version: 4.0.6.2 release   
Hardware: x86-64 (AMD64)   
OS: All   
See Also: https://bugs.freedesktop.org/show_bug.cgi?id=50299
Whiteboard:
Crash report or crash signature: Regression By:
Attachments: some tests on MOD() function
augmented version of test spreadsheet

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
Comment 4 b. 2020-10-19 12:21:16 UTC 
just to clear out some things: 

the provided sheet has another error than wrong calculating 'MOD', the series of input values is broken, starting with 5,6 (displayed) on Feuille2 sometimes values like 5,59999999999999 are stored (either widen the column or watch the input line to spot), 

it's the same for some other bugs filed about MOD, to work correctly it needs correct input, calc sometimes has difficulties producing that, 

most likely this sample results from a weak downfill function, (improved recently, don't know if finally clean now), or from iterative summation of '0,1' which carries a significant imprecision, 

where is 'resolved / anotherbug' in the status dropdowns?