Apache OpenOffice (AOO) Bugzilla – Issue 34085
Formula miscalculates when argument is result of other formula
Last modified: 2013-08-07 15:12:27 UTC
Goal of Cell: The result should be whatever is in the 1/100 place of whatever number is entered. R9 = QUOTIENT( MOD( R10 * 100 ; 10 ) ; 1 ) if R10 == 9.31 then R9 is 1 (CORRECT) but if R10 == 9.04+0.27 then R9 is 0 (INCORRECT) for some reason, if R10 is the literal number, it works, but if R10 is a result of a calculation, even if the calculation results in the same value as the literal number, it fails
Hi Daniel, as this is fom the analysis add in, this seems to be your construction site. Frank
This is a problem with the internal binary representation of floating-point values, where 9.04+0.27 is not exactly equal to 9.31 (the former is slightly less than the latter). The same happens in some other well-known spreadsheet applications... BTW: Why don't you just use "MOD(R10*100;10)" or maybe combined with the ROUND function: "ROUND(MOD(R10*100;10);0)"?
This is RESOLVED? Huh? This code works exactly as it's supposed to in Excel. I've been using the spreadhseet for years to calculate online tax forms for our company. As soon as I switch to OpenOffice, boom, it stopped working. The MOD handles the floating point value just fine. By the time QUOTIENT gets it, the value has nothing to the right of the decimal place. I can send you the Excel spreadsheet if you don't believe me. I haven't changed it one iota since opening it in OpenOffice. I'm sure Bill Gates would be smiling if he read this. ROUND will not work. I'm looking for the exact digit, not a rounded one from the more significant column. I may have made a mistake somewhere, but I don't see how I could use it for so long and have it not work on the first try with OO, and have that be "just how it goes". Thank you for your time, as always. It's appreciated.
@submitter: Yes, please attach your document to this issue and give us some details (i.e. which cells to look at). Thanks
title
Still waiting for a bug document. I have tried in Excel exactly what is described here, and the behaviour is equal to Calc. This issue should be closed without bugdoc.
Sorry I haven't gottten you the doc yet. I was out of the country all last week and now trying to play catch-up. I'll get it to you as fast as I can, hopefully today. Jeff Long President Granite Precision Inc.
And still waiting... I have to close this issue without a test doc...
target->later until bugdoc present, submitter needs more time
I have seen that the last entry was in November and that this issue is OOo-1.1.2 related ... Have you tried this with a newer version of OOo like 1.1.4 or some 1.9.xxx builds? Does your problem occur there also? Or could this issue be closed? I mean, nearly 7 months is a long enough delay, so see this as a reminder, please ... ;)
Hi all I am using OO2.3 and have found out that ther is the same problem my system is a winxp. i noticed the problem when i incerted a calculated number in to a text this can be shown when you have the following spredsheet A1=200.2 A2=200 A3=A1-A2 A4="A1-A3=" & A3 A4 shows then A1-A2=0,199999999999989 i hope this will help Torsten
Created attachment 48883 [details] Example for this bug under created under oo2.3
I encountered this problem too. For instance, for the formula: MOD(0,21;0,1) the result should be 0,01 but actually is 0,00999999999999998 when you increase the number of displayed digits appropriately. This is a problem, as QUOTIENT(MOD(0,21;0,1); 0,01) should be equal to 1, but is equal to 0 because of the problematic floating point representation. This issue still exists in OOo V.2.3 for windows. I haven't checked the linux version yet.
I think, that it cannot be solved inside the code of MOD, because MOD cannot know and cannot determine, how many decimal places you use in your decimal number. But you know it. And therefore you can round the intermediate result to the correct amount of decimal places. For the example =QUOTIENT( MOD( R10 * 100 ; 10 ) ; 1 ) the solution would be =QUOTIENT( ROUND(MOD( R10 * 100 ; 10 )) ; 1 ) For the example =QUOTIENT(MOD(0,21;0,1); 0,01) the solution would be =QUOTIENT(ROUND(MOD(0,21;0,1);2); 0,01) The general problem with precision with binary representation is already tracked in issue 69749. Therefore I suggest to close this issue as WONTFIX, because there is nothing, what can be done inside the MOD-function. [I know the code of MOD, because I have worked on it recently (i59153) to fix an error with integral number in denominator.]