Created attachment 77565 [details] My spreadsheet with data from laboratory, when you will find problem, which I explained. Problem description: Steps to reproduce: 1. Write to cell A1 4,01 or 2,86 or 1,92 or 1,36 or maybe other special (?) numbers... 2. In A2 make formula =A1-INT(A1) 3. In A3 make formula =A2*10 4. In A4 make formula =A3-INT(A3) 5. In A5 make formula =10*A4 6. In A6 make formula =A5-INT(A5) 7. A6 equals 1, which is wrong, because it should equals 0 !!! 8. Check in other cell, that [A5 number]-INT([A5 number])=0. Do not copy formula from A5! Write number from A5 again, using keyboard. 9. Now replace INT with TRUNC([cell];0) and cry when you get 1 instead of 0 !!! Current behavior: In some cases you get wrong number. Expected behavior: Mathematical behavior, which is: If x belongs to natural numbers, x-INT(x)=0 I don't know, whether this problem exists in other versions of Calc and which is the latest proper-working version, if any. Operating System: Debian Version: 3.5.4 release
Hi Łukasz Konieczny, I think is in relation with the size limit of decimal numbers in the application, please take a look to this thread in ask, http://ask.libreoffice.org/en/question/8006/libreoffice-calc-calculation-error/
(In reply to comment #1) > Hi Łukasz Konieczny, > > I think is in relation with the size limit of decimal numbers in the > application, > please take a look to this thread in ask, > http://ask.libreoffice.org/en/question/8006/libreoffice-calc-calculation- > error/ But numbers in this case have only two digits after comma and there are only five steps, of which only three utilises INT function. Please notice, that everything will be good, if you use (in this case) numbers with three digits after comma. You can see that in my .ods file, which I attached to bug report. Calc produced wrong results only in a few cases, only when input data were numbers with two digits after comma. In some two-digits-cases Calc produced proper results, and in all three-digits-cases produced proper results. This behavior is irregular, wrong, and simplicity of input numbers makes it inexplicable (in my opinion, I'm not a programmer) by floating-point problems.
Łukasz, sure I do not explain well the issue, please take a look to this bug, where I think you can find more useful information: https://bugs.freedesktop.org/show_bug.cgi?id=50299
@Kohei - you mind confirming comment 1 and then I assume closing this one as WONTFIX?
Looks like another problem with floating point numbers. As always for everyone using any kind of mathematical computer program reading http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems is a must and should be understood before any serious calculation is performed.
Due to last comment - NOTABUG - this is an issue with hardware limitations. @Markus - thanks for the input here
Although the problem is clearly the hardware limitation (one can perform the steps that OP had described, and set all cells to show 20 decimal digits to see the real representation of the values and understand the underlying cause of this behaviour), here is what I wanted to draw attention to. The INT function (and maybe some others) is used in areas other than those where one deals with microscopic values. I.e., it is difficult to think of a use case where one performed high-accuracy arithmetics with numbers in a range of 10^-5, and then would truncate the result using INT. Thus, in most (if not all) usecases, taking into account these tiny deviations of the hardware representation of actual number is not only awkward, but is a real BUG. Who would care that the result of (4.01-4.0)*100 is represented as 0,99999999999997900000 when INT'ing it? Therefore, I disagree that this is NOTABUG. I set it to REOPENED and Enhancement request (but it may still be classified as a BUG by those more competent than I), and propose to add a threshold of accuracy to such functions (probably customizable somewhere in advanced options in UI, up to current behavior, but set to a reasonable value, say, like 10^(-4), by default). So that INT(0.999) would give 0, but INT(0.9999) would give 1. Of course, this setting need to be kept with the spreadsheet to give consistent results when opening it in another location. This could possibly require changes to ODF standard, and thus be difficult to do.
Markus is one of our lead developers for spreadsheet - if he says not a bug, it's not a bug and Kohei has agreed on the other bug that was mentioned here (and he's our 2nd lead developer on spreadsheet). Closing again as NOTABUG. If you want to get updated standards for ODF - that needs to be discussed with the ODF committee. Please do not reopen the bug. Thanks!
@Kohei & Markus - feel free to change this if you feel it appropriate. But after discussing with both of you in IRC it seemed pretty clear that this would never be implemented, therefore leaving as NOTABUG (perhaps WONTFIX is more accurate?)
*** Bug 141078 has been marked as a duplicate of this bug. ***
*** Bug 133840 has been marked as a duplicate of this bug. ***