There appears to be a bug in the Calc modulo function. Here's the description from Axel Reimer's email entitled "Critical bug in LibreOffice Calc when using mod (showstopper for 3.5.4?)" --- in LibreOffice Calc 3.5.3 there is a critical bug in the mod function. When using =mod=(0,3*100;10) in a cell 3,5527E-015 is displayed as result =mod=(0,4*100;10) in a cell 0 is displayed as result =mod=(0,5*100;10) in a cell 0 is displayed as result =mod=(0,6*100;10) in a cell 7,1054E-015 is displayed as result =mod=(0,7*100;10) in a cell 0 is displayed as result =mod=(0,8*100;10) in a cell 0 is displayed as result =mod=(0,9*100;10) in a cell 0 is displayed as result =mod=(30;10) in a cell 0 is displayed as result =mod=(60;10) in a cell 0 is displayed as result The values for 0,3*100 and 0,6*100 are definitely wrong. This could be a showstopper if this still happens in LibreOffice 3.5.4 RC1. Can someone test if this occurs in 3.5.4 RC1? Best regards, Axel
I have confirmed this bug in LO 3.5.4.1 on ubuntu 11.10 x86_64.
Created attachment 62048 [details] Testcase for bug I'm attaching a simple test document that can be used to test for the bug. Column C shows the formulae used to generate the values in column B. With a correct implementation of the MOD (modulo) function, all of the values in column B should be zero (0).
Effekt is reproducible with "LibreOffice 3.5.4.2 (RC2) German UI/Locale [Build-ID: 165a79a-7059095-e13bb37-fef39a4-9503d18] on German WIN7 Home Premium (64bit) Please ignore formulas in original report (they are wrong), instead please use sample document. I see unexpected remainders in B6, B9, what disappear when I replace Numbers 0,3 and 0,6 by 0,30 and 0,60. But IMHO those are inevitable rounding errors inaccuracies. Those are also visible with AOOo 3.4, EXCEL 2010 and Gnumeric, but it seems EXCEL makes a smarter job during input: when I replace cell Contents "0,3" typing again "0,3" the remainder becomes "0". In LibO That never worked different, also effect reproducible with LibO 3.3.3 @Qubit Please contribute precise descriptions instead of "Terrible horrible Bug in LibreOffice" ;-)
This is a limitation of the FPU. So it's a hardware problem. Anything we try to do at software level would be hacky and prone to other, more serious errors, including significant performance impact. IMO we should leave this alone.
Created attachment 62056 [details] Extremely wrong result @Kohei: thank you for additional clarification. I also believe we can't do much against limitations you mention. But In attached document with chained formulas the result of those rounding errors becomes ridiculous (same in Gnumeric, Excel not yet tested), what might be an interesting research for a SOC student or similar?
We do try to correct the most obvious cases, which may be something we could do here as well. But since the real cause for this problem is technically unsolvable, whoever wants to "solve" it may end up spending a lot time for a very little gain. IMO this is something the users need to be aware of. The limitation applies to all software that runs on current hardware, not just spreadsheet. Some mathematical and statistical software overcome this by not using FPU and doing it all at software level. But we can't afford to do that, or else the performance of formula calculation will end up 1000 times slower.
@Rainer Bielefeld Re: Bug subject -- I was just trying to copy what the original reporter had written ("here's the description from x's email..") so as not to misrepresent his work. That's why I followed up my original bug report with a test case and a more precise statement ("all of the values in column B should be zero (0)"). I just didn't want to rain on original source's parade as he's the one who made the first submission. Suffice it to say that I will be much more ruthless when I edit community-submitted bug reports in the future.
--- IMO this is something the users need to be aware of. The limitation applies to all software that runs on current hardware, not just spreadsheet. --- Where should this go in user documentation? A general note on spurious results attributable to use of binary-encoded decimal numbers? Maybe some examples? --- Some mathematical and statistical software overcome this by not using FPU and doing it all at software level [making it] 1000 times slower. --- Is it worthwhile to consider a switch to toggle Calc into "high precision" or "correct results" mode? Are there many use-cases in which it would be beneficial to trade high speed for mathematical accuracy? Here's a related article from Microsoft's knowledge base entitled "How to correct rounding errors in floating-point arithmetic" -- advising MS Excel users, etc... http://support.microsoft.com/kb/214118
(In reply to comment #8) > --- > Some mathematical and statistical software overcome this by not using FPU and > doing it all at software level [making it] 1000 times slower. > --- > > Is it worthwhile to consider a switch to toggle Calc into "high precision" or > "correct results" mode? It's perhaps easier if you give that a try, and draw your own conclusion. I've already said what I had to say.
Removing myself from CC.
As expected same problem in Excel 2010. @Qubit 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. Concerning a warning hint: Please read the license! That's all we can do. I am one of the generation where engineers use slide rules (I'm still using it today for my work). Its precision is a little below CALC, but I am very successful with it and never read a warning hint "If you use this tool for construction of a bridge ...."
(In reply to comment #9) > (In reply to comment #8) > > > --- > > Some mathematical and statistical software overcome this by not using FPU and > > doing it all at software level [making it] 1000 times slower. > > --- > > > > Is it worthwhile to consider a switch to toggle Calc into "high precision" or > > "correct results" mode? > > It's perhaps easier if you give that a try, and draw your own conclusion. I've > already said what I had to say. I assume that must be a joke, as it can't possibly be easier to re-write and rebuild major parts of LibreOffice than it can be to have a short and civil discussion about a feature request to Calc. I'm personally fine with this behavior of Calc as it stands today. I was just proposing a possible change on the grounds that it might improve the software experience for some subset of our users. Short and sweet.
> As expected same problem in Excel 2010. Interestingly, no such problem in Excel 2003 (converted the sample ods to xls, opened in Excel and pressed F9)
(In reply to comment #11) > > Concerning a warning hint: > Please read the license! That's all we can do. I am one of the generation where > engineers use slide rules (I'm still using it today for my work). Its precision > is a little below CALC, but I am very successful with it and never read a > warning hint "If you use this tool for construction of a bridge ...." well thankfully slide rules never had to come with a shrink-wrap/click-wrap license :-) My physics teacher in high school had a big wall-mounted slide rule and would race us if we weren't moving fast enough on calculations. I've never used one in my work, but I still have a couple that were getting tossed, and one that sat by my grandfather's drafting table. Regarding a warning for users, I do think it could be helpful for the average user to know that floating point representations/calculations can introduce subtle errors. If we don't want to put it in the docs for LO specifically, perhaps it should be mentioned in some kind of more general-purpose 'intro to computing' book that I can recommend to new computer users. Maybe the people at FLOSSManuals have something like that already.
*** Bug 71131 has been marked as a duplicate of this bug. ***
*** Bug 73366 has been marked as a duplicate of this bug. ***
*** Bug 75811 has been marked as a duplicate of this bug. ***
*** Bug 87386 has been marked as a duplicate of this bug. ***
*** Bug 87506 has been marked as a duplicate of this bug. ***
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.
Created attachment 111126 [details] Spreadsheet for testing modulo problem.
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).
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.
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.
*** Bug 89373 has been marked as a duplicate of this bug. ***
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
(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.
(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.
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.
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.
...now listed as...
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 (b00000000000000000000000000000111 & num) { num &= 11111111100011111111111111111111; }
Created attachment 124437 [details] MOD and MODFAST behaviour proposition
This seems to have been fixed, likely with the changes for bug 102742.
Created attachment 130588 [details] 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.
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.
(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.9999999, 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.
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.99999999999999 and 0.0899999999999999, respectively; and if the cells are tested against each other for equality, MOD(24.09,1)*100 = 8.99999999999999 = 9 and MOD(24.09,1) = 0.0899999999999999 = 0.09 and 9 8.99999999999999 = 0 and . But 8.99999999999999 ≠ 9 and 0.0899999999999999 ≠ 0.09
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.99999999999999 and 0.0899999999999999, 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.99999999999999 = 9 and MOD(24.09,1) = 0.0899999999999999 = 0.09 and (9 – 8.99999999999999) = 0 and (0.09 – 0.0899999999999999) = 0. However: 8.99999999999999 ≠ 9 0.0899999999999999 ≠ 0.09 (9 – 8.99999999999999) ≠ 0 (0.09 – 0.0899999999999999) ≠ 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).
Comment 38 can be deleted as incomplete.
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
Created attachment 166404 [details] 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,0899999999999999, 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,99999999999999 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.
Created attachment 166405 [details] a proof of concept to correct (some) fp-conversion errors by smart rounding sorry, attached outdated version of sample, this should work better ...