Apache OpenOffice (AOO) Bugzilla – Issue 75536
Quotient and rest calculations
Last modified: 2013-08-07 15:14:13 UTC
The calculation of quotient and remainder should follow their definition in the following theorem: Given two integers a and d, with |d| ≠0 There exist unique integers q and r such that a = qd + r and 0 ≤ r < |d|, where |d| denotes the absolute value of d. The integer * q is called the quotient * r is called the remainder * d is called the divisor * a is called the dividend while openoffice respect the theorem for d>0 some problem arise when both a and d are less then 0. Ie q=quotient(-5;-3)=1 r=rest(-5;-3)=-2 But according to the previous definition it should be 0 ≤ r < | d |. from a mathematical point of view the correct solution should be: q=quotient(-5;-3)=2 r=rest(-5;-3)=1 as can be verified by: -5=-3*2+1 Also MS Excel behaves in the same (wrong) way. A possible solution could be to keep compatibility with excel to implement an optional mode parameter like in roundup function.
I think that actually openoffice implement the following to calculate quotient: if a/b < 0 q=roundup(a/b) if a/b > 0 q=rounddown(a/b) to respect the theorem it should be if a<0 q=roundup(a/b) if a>0 q=rounddown(a/b) with the rest then calculated as r=a-(b*q)
Sorry the reference for "mode" parameter was wrongly to roundup function but should be to ceiling one.
romolo, if we behave the same way as that other spreadsheet, then developers are unlikely to change the way Calc works even if that is not mathematically correct. Customers just have to deal with this. Sorry.
Openoffice doesn't behaves as other spreadsheet at least for quotient: quotient(5;10) Openoffice 0, Excel2003 0, Gnumeric 0 quotient(-5;10) Openoffice -1, Excel2003 -0, Gnumeric 0 quotient(5;-10) Openoffice -1, Excel2003 -0, Gnumeric 0 quotient(-5;-10) Openoffice 0, Excel2003 0, Gnumeric 0 The rest function behaves like in excel Excel's invention of the concept of -0 apart, I think openoffice should definitely choose whenever to follow compatibility (fully) or correctness.
Confirming with OO 2.2m14 on WinXP - speadsheet I am attaching will show numbers in A1 and A2 that rae different between Excel and Calc. For Excel to have Quotient function "Analisys toolpack" needs to be activated via Tools\Addons.
Created attachment 43905 [details] Testdoc
Nevertheless excel implementation is wrong as least when you put quotient and rest together: trying all combinations of 5 (dividend d) and 10 (divisor D) with different sign in excel we get for quotient (Q) and rest (R) d D Q R 5 10 0 5 -5 10 -0 5 5 -10 -0 -5 -5 -10 0 -5 Apart the very funny invention of the concept of -0 in excel, now if we apply the rule d=D*q+r we get that is verified only for the first and the fourth expression not for the second and the third. at least in openoffice you have: d D Q R 5 10 0 5 -5 10 -1 5 5 -10 -1 -5 -5 -10 0 -5 that verify the condition anytime. for math correctness you should have: d D Q R 5 10 0 5 -5 10 -1 5 5 -10 0 5 -5 -10 1 5 Should openoffice follow compatibility to such a dumb program ? Please don't change quotient to make it compatible with excel !!!!
Hi, not a defect as it works as intended. Eike do you agree ? Something for the Spec. ? Frank
Romolo, > The calculation of quotient and remainder should follow their definition in the > following theorem: Why? > Given two integers a and d, with |d| ≠0 > There exist unique integers q and r such that a = qd + r and 0 ≤ r < |d|, where > |d| denotes the absolute value of d. Please note that this is a _theorem_. Given ... there are ... that fulfill the requirement of ... Nothing more, especially not a definition of a rule of how quotient and remainder should be calculated generally. Another theorem is: If a and d are integers, with d non-zero, then a remainder is an integer r such that a = qd + r for some integer q, and with 0 ≤ |r| < |d|. Taking the wording of the theorem you gave I assume it originated from http://en.wikipedia.org/wiki/Division_algorithm Please then also have a look at http://en.wikipedia.org/wiki/Remainder and especially http://en.wikipedia.org/wiki/Modulo_operation The Euclidean definition seems appealing for its regularity, but that's just not the definition used in the wild. Spreadsheet implementations usually define the remainder/modulo result to have the same sign as the divisor. At least Excel, Gnumeric and Calc do. Anyway, for the examples you gave later > quotient(-5;10) Openoffice -1, Excel2003 -0, Gnumeric 0 > quotient(5;-10) Openoffice -1, Excel2003 -0, Gnumeric 0 the OOo results are wrong and this has to be corrected. @fst: please don't assign issues about questionable calculation of function results to 'requirements'. Either we have to fix things because we do it wrong, or if changes of definitions are required the OASIS subcommittee has to agree upon a standard for the ODFF (OpenFormula) work. Thanks Eike
Daniel, That's AnalysisAddIn::getQuotient(), looks like approxCeil() should be applied if exactly one of the arguments is negative. Thanks Eike
Eike, The spreadsheet implementation sign(r)==sign(d) brings funny result for rest as it happens in excel2003; if you implement his rule for the rest without taking into account the quotient you really do something wrong. Excel really put the rest of the same sign of the divisor, but doing so the condition a=qd+r is not satisfied in the example written in previous reporting. Whatever is the way you want to solve this issue, please save the math and do not follow excel2003 as -5!=5 and 5!=-5. In any case it will bring incompatibilities with excel's bugs; but the question then is: should be openoffice compatible with excel at bugs level ? Please note that gnumeric, to cut the gordian knot, doesn't seem to have a rest/remainder function. Thanks, Romolo
Additional note: I have just seen that gnumeric has a function mod(a;b) which is used for rest(a;b) while importing xls documents; nevertheless also mod(a;b) brings bad results that do not satisfy a=qd+r. Romolo
change target from 2.x to 3.x according to http://wiki.services.openoffice.org/wiki/Target_3x
Created attachment 50800 [details] one patch to issue75536
Added patch to SRC680/dr58 (OOo 2.4)
back to QA
verified in internal version cws_dr58
found integrated on master OOHm5 using Solaris, Linux and Windows build