Issue 34085 - Formula miscalculates when argument is result of other formula
Summary: Formula miscalculates when argument is result of other formula
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1.2
Hardware: PC Windows 2000
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on:
Reported: 2004-09-11 16:19 UTC by jefflong
Modified: 2013-08-07 15:12 UTC (History)
4 users (show)

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

Example for this bug under created under oo2.3 (6.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-10-14 23:09 UTC, torte782000
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jefflong 2004-09-11 16:19:45 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)
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
Comment 1 frank 2004-09-13 09:57:02 UTC
Hi Daniel,

as this is fom the analysis add in, this seems to be your construction site.

Comment 2 daniel.rentz 2004-09-13 11:42:08 UTC
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

BTW: Why don't you just use "MOD(R10*100;10)" or maybe combined with the ROUND
function: "ROUND(MOD(R10*100;10);0)"?
Comment 3 jefflong 2004-09-13 13:00:11 UTC


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.
Comment 4 daniel.rentz 2004-09-13 13:15:17 UTC
@submitter: Yes, please attach your document to this issue and give us some
details (i.e. which cells to look at). Thanks
Comment 5 daniel.rentz 2004-09-13 15:14:50 UTC
Comment 6 daniel.rentz 2004-09-28 16:20:54 UTC
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 
Comment 7 jefflong 2004-09-28 16:46:21 UTC
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

Jeff Long
Granite Precision Inc.
Comment 8 daniel.rentz 2004-11-03 17:14:51 UTC
And still waiting... I have to close this issue without a test doc...
Comment 9 daniel.rentz 2004-11-03 20:22:52 UTC
target->later until bugdoc present, submitter needs more time
Comment 10 thackert 2005-05-29 06:06:48 UTC
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 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 ... ;)
Comment 11 torte782000 2007-10-14 22:02:04 UTC
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

A4="A1-A3=" & A3

A4 shows then A1-A2=0,199999999999989

i hope this will help

Comment 12 torte782000 2007-10-14 23:09:45 UTC
Created attachment 48883 [details]
Example for this bug under created under oo2.3
Comment 13 rpbyc 2007-11-20 11:03:32 UTC
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.
Comment 14 Regina Henschel 2009-05-13 20:06:32 UTC
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