[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

b.  changed:

   What|Removed |Added

 Attachment #166404|0   |1
is obsolete||

--- Comment #43 from b.  ---
Created attachment 166405
  --> https://bugs.documentfoundation.org/attachment.cgi?id=166405=edit
a proof of concept to correct (some) fp-conversion errors by smart rounding

sorry, attached outdated version of sample, this should work better ...

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

b.  changed:

   What|Removed |Added

 CC||newbie...@gmx.de

--- Comment #42 from b.  ---
Created attachment 166404
  --> https://bugs.documentfoundation.org/attachment.cgi?id=166404=edit
a proof of concept to correct (some) fp-conversion errors by smart rounding

posting here as i couldn't find or overlooked the 'new report', 

proposal for better results at the bottom of this post, 

the OP bug seems! fixed in ver. 7.1, sample
libreoffice_bug-50299_test-document-showing-modulo-bug.ods calculates
correctly, 

but try mod(24,09;1), expand the result cell until you see 0,0899,
it is still buggy, 

results in newsample.ods: evolve from invalid summation, at least 0,2+0,1 and
0,7+0,1 inject small 'decimal to binary-float conversion artefacts', mod just
makes them visible, sample produces better results in ver. 7.1, 

libreoffice_bug-50299_modulo-bug.ods: impressive how much work already was
invested in theese issues, calculates better in ver. 7.1, needs a recalc to
clear outdated results loaded from file, 

in 'mod and modfast proposition.ods' i see some fp-imprecision artefacts with
divisors 3, 4, 6, 7, 8 and 9, not clean with ver. 7.1, clean if you use 'MOD_S'
from attached sample, check red and green marked cells D27 vs. F27, and
F30:F32, 

BugDecimalDateTest.ods: just expand col H until you see 8,99 in the
critical cells, the date function looks like more doing cut or truncation of
it's arguments than rounding, and thereby aggravates the error and makes it
visible, but the source cause is the inaccurate decimal -> fp conversion, or
the inaccurate [overaccurate ;-) ] MOD() result, this still happens in ver.
7.1, 

@Rainer Bielefeld wrote - long time ago and prophetical: 
'All this does not mean that it's impossible to use hardware with limitations
in a more smart way, but that's not a simple fix. Research will go on, may be
some day someone will find a way to sail around those reefs.' 

i'm not going to stick that badge to my jacket, but i'd like to push
improvements, either enable a 'decimal-safe' datatype in calc, or take
something like the 'smart rounding' shown in the attached sheet (with 'user
code functions') implement it in code - and faster - (looking up the decimal
places of the arguments and rounding results to the mathematical correct
length), 

that will surely have a negative performance impact, but irritated users asking
correct questions about incorrect results ever and ever again are a performance
issue as well - imho the bigger one - 

if you want to satisfy all wishes just implement an option switch for
'performant vs. precise' calculation, then it's up to the user to choose his
poison, 

the option 'precision as shown' is already an attempt in that direction, it
covers plenty problems, but not all, and it introduces new ones because it is
only useful in cases where the user can make reasonable assumptions about the
number of digits in the results, other tasks are made worse, especially if the
user isn't aware / has forgotten that this function is active, 

i would really appreciate if someone takes the time to check the approach with
the macros, are there errors?, 'just try to break it' ... 

@Rainer Bielefeld: i liked slide rulers too, they are precise enough for
bridges (calculate to five digits and then double the result to stay safe), and
they avoid 'unintelligent' cutting, rounding, truncating made by machines but
use 'smart rounding' by humans in analog, that works.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #41 from Aron Budea  ---
Could you open a new bug report on this newly mentioned issue, and repeat your
findings there? (and refer to the new report in a comment so we know about it)
The originally reported issues have been fixed, and it's easier to track this
other one separately.

I'd also suggest installing the latest version when testing (even if it's only
a release candidate, like 5.3.0.2 now), it can be done separately from your
installed version as described in [1].

[1] https://wiki.documentfoundation.org/Installing_in_parallel/Linux

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #40 from k...@cox.net ---
Comment 38 can be deleted as incomplete.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #39 from k...@cox.net ---
My apologies, Aron Budea, Like your normal users, I use what apt, apt-get or
the synaptic package manager give me from the (X)Ubuntu repositories. In the
future, I will not respond until my version of LO Calc is the same as or newer
than the last version mentioned as tested.

To expand on your "main issue", the calculated values of MOD(24.09,1)*100 and
MOD(24.09,1) are displayed in general number format as 9 and 0.09,
respectively. When they are copied to another document (e.g., gedit), they are
pasted as 9 and 0.09, respectively, and when their values are copied to other
cells, they are pasted as 8.99 and 0.0899,
respectively. This is acceptable for WYSIWYG and Excel does the same. However,
if the cells are tested against each other for equality, 
MOD(24.09,1)*100 = 8.99 = 9 and MOD(24.09,1) = 0.0899 =
0.09 and (9 – 8.99) = 0 and (0.09 – 0.0899) = 0.

However:   8.99 ≠ 9
 0.0899 ≠ 0.09
 (9 – 8.99) ≠ 0
(0.09 – 0.0899) ≠ 0

Calculations should be done on actual values if ROUND() is not used, so if the
above applies to the current version of LO Calc, something is still wrong (in
medicine or finance, a calculation that is randomly off by one month is a
serious error).

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #38 from k...@cox.net ---
My apologies, Aron Budea, I only use the versions of LibreOffice Calc that the
(X)Ubuntu repositories give me using sudo apt, sudo apt-get or the synaptic
package manager. However, to expand on your "main issue", the calculated values
of MOD(24.09,1)*100 and MOD(24.09,1) are displayed in general number format as
9 and 0.09, respectively. When they are copied to another document (e.g.,
gedit), they are pasted as 9 and 0.09, respectively; when their values are
copied to other cells, they are pasted as 8.99 and
0.0899, respectively; and if the cells are tested against each
other for equality, 
MOD(24.09,1)*100 = 8.99 = 9 and MOD(24.09,1) = 0.0899 =
0.09 and 9 8.99 = 0 and . But 8.99 ≠ 9 and
0.0899 ≠ 0.09

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #37 from Aron Budea  ---
(In reply to klsu from comment #36)
> Using the above test file dated 2014-12-21 19:00 CET in Version 5.1.4.2,
> Build ID: 1:5.1.4-Oubuntu1, the spreadsheet is still showing 143 of 1000
> results not equal to zero, 72 of which are significant; so the problem still
> exists.

5.1.4.2 is quite old, if you check the bug report mentioned by Eike, the fix is
in 5.3. Testing with 5.3.0.2 and doing a hard recalc on the fields shows no
errors.

> The problem that lead me to the discovery of this one also still occurs in
> Version 5.1.4.2, Build ID: 1:5.1.4-Oubuntu1. That involved converting months
> and displayed as decimals (i.e., European format) to U.S. format in a
> spreadsheet in which everything else is in U.S. format. I've added an
> example of that.

This is quite interesting, the main issue is:
=MOD(24.09;1)*100 is 8.999,
while:
1) 0.09*100 is 9,
2) =MOD(24.09;1) is 0.09.

There's probably a reasonable explanation for it in the handling of floating
point numbers, hopefully someone else can shed some light on that.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #36 from k...@cox.net ---
Using the above test file dated 2014-12-21 19:00 CET in Version 5.1.4.2, Build
ID: 1:5.1.4-Oubuntu1, the spreadsheet is still showing 143 of 1000 results not
equal to zero, 72 of which are significant; so the problem still exists.

The problem that lead me to the discovery of this one also still occurs in
Version 5.1.4.2, Build ID: 1:5.1.4-Oubuntu1. That involved converting months
and displayed as decimals (i.e., European format) to U.S. format in a
spreadsheet in which everything else is in U.S. format. I've added an example
of that.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #35 from k...@cox.net ---
Created attachment 130588
  --> https://bugs.documentfoundation.org/attachment.cgi?id=130588=edit
Conversion to DATE using MOD to get month from 2 decimal places.

Example showing equalities that are not and date conversions that are incorrect
when MOD is used in certain ways.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

Eike Rathke  changed:

   What|Removed |Added

 Status|REOPENED|RESOLVED
   See Also|https://bugs.freedesktop.or |https://bugs.documentfounda
   |g/show_bug.cgi?id=87875,|tion.org/show_bug.cgi?id=10
   |https://bugs.documentfounda |2742
   |tion.org/show_bug.cgi?id=81 |
   |337,|
   |https://bugs.documentfounda |
   |tion.org/show_bug.cgi?id=84 |
   |435 |
 Resolution|--- |FIXED

--- Comment #34 from Eike Rathke  ---
This seems to have been fixed, likely with the changes for bug 102742.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #33 from mahfiaz  ---
Created attachment 124437
  --> https://bugs.documentfoundation.org/attachment.cgi?id=124437=edit
MOD and MODFAST behaviour proposition

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #32 from mahfiaz  ---
I think this bug should be closed and reported again.

It would make sense to always round numbers with MOD (lets not forget how fast
modern CPUs actually are) and maybe add MODFAST function which could make
available the behaviour we have now.

So I propose MOD would have similar behaviour:
A3 - dividend
B3 - divisor
C3 - precision =-CEILING(LOG(B3; 10)) + 10
D3 - =MOD(ROUND(A3; C3); ROUND(B3; C3))


I am no expert but similar behaviour could possibly be achieved with just
bitmasking a few last bits off from the fraction part of the doublefloat if the
fraction part is filled eg some of its upper bits (the rightmost ones) are
nonzero.

if (b0111 & num) {
num &= 1000;
}

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #30 from k...@cox.net ---
Not sure why this is not listed as an enhancement. If something that's supposed
to perform a certain calculation gives the wrong answer every once in a while,
it's broken. When fixing something is an enhancement, expect to be charged
extra for getting a headlight fixed, because it's an enhancement.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #31 from k...@cox.net ---
...now listed as...

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

Julien Nabet serval2...@yahoo.fr changed:

   What|Removed |Added

   See Also||https://bugs.documentfounda
   ||tion.org/show_bug.cgi?id=84
   ||435

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

Robinson Tryon (qubit) qu...@runcibility.com changed:

   What|Removed |Added

   See Also||https://bugs.documentfounda
   ||tion.org/show_bug.cgi?id=81
   ||337

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #27 from k...@cox.net ---
(In reply to Jean-Baptiste Faure from comment #26)
 For me MOD() function should be used only with integers. With implementation
 in LibreOffice, and if you are sure that the dividende is integer, you can
 use an expression like =MOD(ROUND(0.9*100;0);10).
 
 I think it is a bad idea to generalize MOD() function to non-integer
 numbers. The bug is here.
 
 Best regards. JBF

For you perhaps; but there are legitimate uses of MOD() that involve real
numbers. Furthermore, the interim calculation performed by MOD() is expected to
be a real number, because it it were not, the result of MOD() would always be
zero. The problem being discussed here is not caused by the type of input, but
by how the MOD() function, as implemented in LO (and all other Linux
spreadsheets I have tried), deals with the facts that calculations performed by
the spreadsheet are real number calculations, and their results are often
inexact in the last three digits used.

You appear to be saying that every use of MOD() should look like this:
=MOD(ROUND([dividend],[n]),[divisor]) ...or...
=MOD([dividend],ROUND([divisor],[n])) ...or...
=MOD(ROUND([dividend],[n]),ROUND([divisor],[n])) ...instead of...
=MOD([dividend],[divisor])

The LO implementation of the MOD() function appears to make the incorrect
assumption that the interim results of its calculations can be used without
regard to the fact that digital calculations with real number cause precision
loss in the last 3 or 4 digits. As with human thought, incorrect assumptions
can lead to wrong answers.

Changing the instructions for using a function does not correct a bug. Users
will not expect to have to read the instructions for every function they have
used in Excel for years, before using them in LO (etc.). Programmers seem to
not understand that the computer, and therefore the software, is supposed to
allow the user to work efficiently. When software forces the user to work a
certain way, the programmer is making the incorrect assumption that he/she
knows the user's job as well as the user does.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #28 from Robinson Tryon (qubit) qu...@runcibility.com ---
(In reply to klsu from comment #27)
 Changing the instructions for using a function does not correct a bug. Users
 will not expect to have to read the instructions for every function they
 have used in Excel for years, before using them in LO (etc.).

Specifically on this point, I'd say that LibreOffice is not Excel. If there are
compelling mathematical reasons to implement a function or a feature in a
similar fashion to another piece of software (e.g. Excel, Abiword, Sage,
etc..), then I think it's valuable for us to consider it, but sometimes Excel
is just wrong (Classic example: the '1900' leap-year bug).

 Programmers
 seem to not understand that the computer, and therefore the software, is
 supposed to allow the user to work efficiently. When software forces the
 user to work a certain way, the programmer is making the incorrect
 assumption that he/she knows the user's job as well as the user does.

One potential use-case for Calc would be as a generic tool for numerical
computation (with the modulus function operating without the limitations
described above). I don't think that the Calc developers currently see that as
a primary goal of the software, so if there is a disconnect between their view
of the tool and how our users see it, perhaps we need to sit down and figure
out how to reconcile these two groups.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

--- Comment #29 from k...@cox.net ---
If making sure software doesn't return random wrong answers isn't a compelling
reason to fix it, I don't know what is. The 1900 leap year bug is a good
example of why I want to get away from Excel (new versions cost as much as the
old ones, have the same bugs, no meaningful improvements, and now are getting
worse). At least the 1900 leap year bug gives the wrong answer for every date
earlier than 1 March 1900, in a small range of dates. Since it doesn't accept
dates = 0 it's potential damage and utility are predictable and relatively
easy to account for should someone need those few dates. Neither is the case
with problems caused by how precision is handled.

If Calc developers currently do not see consistently correct answers as a
primary goal of software intended as a generic tool for numerical computation,
then they do not understand the requirements of jobs that require calculations.
The law, customers of accountants, engineers and medical people, and the laws
of physics are not tolerant of wrong answers. Word Processors, Presentation,
Publication and Graphics software that don't do exactly what they're supposed
to do won't generally lead to loss of life or property. Wrong answers to
calculations can easily lead to both. I'm not sure Calc developers are aware of
the extent to which their lives are dependent every day on calculations being
correct...ceilings overhead, bridges and floors below, poisons in
prescriptions, accurate bank statements.

I have no idea how the different views of programmers and users can be
reconciled on the open source side. A software developer's CEO would be stupid
to risk a court battle over a loss caused by a bug like this; he'd get it fixed
ASAP. However, with open source, there is no contract between user and
developer, and no CEO to order it fixed, so the only incentive for the
developer to fix something is the developer's level of professionalism. It is
obvious from how some of the features in LO Calc's Paste Special operation
are implemented that the people making the development decisions are not
serious users of spreadsheets. But business users don't have time to try to
explain to someone who is not a serious user why something is a problem, so
they either learn to work around the quirk, or give up on the software.

I can't help improve software if the developers don't use their own software
enough to recognize something that's wrong when it's pointed out to them; and
if they did use it enough, they'd notice the problems themselves.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

m.a.riosv mari...@miguelangel.mobi changed:

   What|Removed |Added

 CC||andis.lazd...@gmail.com

--- Comment #25 from m.a.riosv mari...@miguelangel.mobi ---
*** Bug 89373 has been marked as a duplicate of this bug. ***

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=50299

Jean-Baptiste Faure jbfa...@libreoffice.org changed:

   What|Removed |Added

 CC||jbfa...@libreoffice.org

--- Comment #26 from Jean-Baptiste Faure jbfa...@libreoffice.org ---
For me MOD() function should be used only with integers. With implementation in
LibreOffice, and if you are sure that the dividende is integer, you can use an
expression like =MOD(ROUND(0.9*100;0);10).

I think it is a bad idea to generalize MOD() function to non-integer numbers.
The bug is here.

Best regards. JBF

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=50299

--- Comment #24 from k...@cox.net ---
2015-01-21, thinking about doing income taxes soon, installed Open Office
4.1.1, Gnumeric 1.12.6, and Calligra Sheets 2.7.2 and determined that this
problem exists in all but Calligra Sheets; however a related problem with the
DATE() function (bug 87386) exists in all of them (Calligra sheets has better
Paste Special, but a lot of other problems). The Excel spreadsheets I've used
for past years' taxes have definitely made use of the DATE() and MOD()
functions, but Excel gives the correct answers. If you believe that if the
I.R.S. found an error in a return, they'd accept the excuse I used
OfficeLibre/Open Office/Gnumeric/Calligra Sheets, which has bugs that can
result in calculation errors with some functions, dream on...

I very quickly noticed that although there are problems with OpenOffice Calc
that do not exist in LibreOffice Calc, the two versions are pretty much the
same (not a surprise). People who use spreadsheet programs require the same
things of them, so why are they separate, and why are still other open source
spreadsheets out there (of the 10, excluding web based ones, listed by
Wikipedia, several no longer exist)? Doesn't anyone in the open source
community understand how incredibly inefficient it is for different groups of
people to work on different programs that must do pretty much the same things
in the same ways? By dividing itself, it allows itself to stay beaten.

Engineers, accountants and doctors can't use computational software that has
features that don't work properly. If a structural collapse or medication error
kills people, or money is lost because financial calculations are wrong, our
society will not accept software bugs as an excuse.

Since LibreOffice/OpenOffice, Gnumeric and Calligra have been around for years,
and all still give wrong answers, not only does there appear to be no suitable
alternative to Microsoft Excel for serious spreadsheet use, but the resources
being applied to creating one are being spread in so many different directions
that none is likely to work in the foreseeable future. The result is that users
who want to change to get away from the hook-them-and-then-soak-them philosophy
of commercial software development and marketing, can't; they have no viable
alternative.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=50299

A (Andy) stgohi-lob...@yahoo.de changed:

   What|Removed |Added

   See Also||https://bugs.freedesktop.or
   ||g/show_bug.cgi?id=87875

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=50299

--- Comment #23 from k...@cox.net ---
Just to remind whoever is calling this bug an enhancement of low importance:
the criteria by which importance is decided assume that the worst things that
can happen are crashes and severe system slow-downs. Perhaps that is so in
virtual reality. In the real world there is something worse in a spreadsheet:
inconsistent and wrong answers.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=50299

k...@cox.net changed:

   What|Removed |Added

 Status|RESOLVED|REOPENED
 Resolution|NOTABUG |---
Summary|MOD shows not existing  |MOD shows not existing,
   |small remainder with|inconsistent, small
   |calculated Dividend |remainder with calculated
   ||Dividend
 Ever confirmed|0   |1

--- Comment #20 from k...@cox.net ---
in cell A6 enter 0.3
in cell B6 enter =MOD(A6*100, 10)
cell B6 displays 3.5527136788005E-015
in cell A6 enter =3/10
cell B6 displays 0

So letting the hardware create the 0.3 instead of using LO Calc's stored 0.3
gives the correct answer. That doesn't prove it's not a hardware bug, but it
proves it is or is also a software bug, and I have confirmed that it exists in
LO 4.4.0 Beta Dev Daily I installed 2014-12-18 AND in Excel 2003, but not as
bad. More on that when I reopen 87506 with a spreadsheet example showing just
how bad the LO Calc MOD() function can be.

Same result using 0.6 vs 6/10

We can't fix it or don't think it's worth fixing, so mention it in the
documentation and it's resolved does not resolve the problem. Explaining it to
the average person wouldn't help them (if they ever happen to read it - have
you read, understood and remembered all the documentation of every piece of
software you use?). They would understand if you use the MOD() function, the
answer might be right and it might not be, so you'd better check every use of
the MOD() function manually. It means don't use MOD(), it's not reliable.

The correct resolution would be to remove the MOD() function from LO Calc until
it has been fixed.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=50299

--- Comment #21 from k...@cox.net ---
Created attachment 26
  -- https://bugs.freedesktop.org/attachment.cgi?id=26action=edit
Spreadsheet for testing modulo problem.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 50299] MOD shows not existing, inconsistent, small remainder with calculated Dividend

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=50299

--- Comment #22 from k...@cox.net ---
in cell A1 enter 80.9
in cell A2 enter 81
in cell A3 enter 81.1
in cell B1 enter =MOD(A1*100,10)
copy it to range B2:B3
B1 = 9.09494701772928E-013
B2 = 0
B3 = 10

Increasing in increments of 0.1, between 64.4 and 82.1, this occurs 36 times.
The error in B1 might not hurt anything, but in engineering or medicine, the
error in B3 could be fatal, and in finance it would be unacceptable.

The same thing in Excel (2003) gives:
B1 = 9.09495E-13
B2 = 0
B3 = -9.09495E-13

This calculation performed in LO Calc 4.4.0 Beta2 Dev Daily from 2014 12 18,
from 0 to 100 in increments of 0.1 results in 143 such errors out of 1000, the
maximum error being 10, the minimum being 1.13686837721616E-013. In Excel 2003,
it also results in 143 errors, but the absolute value of no error exceeds
9.09495E-13. The pattern obvious in the errors suggests the way LO and Excel
calculate the MOD() are significantly different; Excel is accounting for the
fact that small errors the digits not considered significant can cause a result
to be below the actual value, so simply taking the integer of a real value when
performing the MOD() won't work properly.

I have no way of knowing where the problem is, but there is clearly a problem
related to rounding error that can result in serious errors, and it's not
caused by hardware.

When using with the attachment, beware; when I changed A8  A7 from 100  10 to
10  1, or changed A7  A8 from 1  10 to 10  100, respectively IN THE ORDER
SHOWN, recalculation caused strange results in B11 or B12 that required a
forced recalculation (Ctrl+Shift+F9).

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs