[Libreoffice-bugs] [Bug 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.documentfoundation.org/show_bug.cgi?id=87386
b. changed:
What|Removed |Added
CC||newbie...@gmx.de
--- Comment #15 from b. ---
in fp-math '=24,09 - 24' and thus '=mod(24,09;1)' unfortunately results in
8.986E-2, it's the effect of 'cancellation', and not avoidable by
successively subtracting '1', the step '=2,09 - 1' will fail too,
and calc doesn't apply rounding on date, year, month, day, hour or minute
values but 'truncation', rounding is only performed on seconds, there violating
it's own 'wall clock concept',
self-help for troubled users: round to meaningful amount of decimals digits,
e.g. '=Mod("value with two decimal places"; integer)' can have at most two
decimal places in the result, 'healing rounding', if you get the 'cut'
somewhere between the needed value and the fp-artifacts you are done,
or try 'literal calculations' with the digits given in the string,
'=RIGHT(C13;2)' or '=VALUE(RIGHT(C13;2))' would make it for this case,
idea for programmers: it is an old problem, concerning many areas /
calculations, and causes many irritations up to quarrels, whoever comes first
with a universal solution 'around the corner' will reap fame and glory, i don't
think the task is proven as 'unsolvable' ...
would like to reopen, but would also like to 'not enrage' @Matthew Francis,
choosing the latter ... ;-)
@k...@cox.net: 'Some people need to learn that computers and software are
supposed to work for people.' - thumbs up
besides: repro in 7.2.0.0.alpha0+
--
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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.documentfoundation.org/show_bug.cgi?id=87386 --- Comment #14 from k...@cox.net --- (In reply to Matthew Francis from comment #12) To repeat, as a member of LibreOffice QA, I am closing this bug. Reopening it will not change the outcome. When I write an application for other people's use, I want to know what's wrong with it, so I can fix it ASAP. That's how professionals work. On the assumption that the people working on LibreOffice take pride in their work, I reported the problems I found. I've done all I can by reporting the bugs, by telling anyone I know to stay away from LibreOffice Calc, and by not using it myself for work that must be correct. Other users who aren't as careful might not notice there's a problem, but if either of these bugs ever causes loss of life or financial loss, the law may force them to figure out why, and then LibreOffice developers will probably hear about it as well. If you want to close the bug, it's obviously your prerogative, however, pretending it's not a bug doesn't resolve it. It sounds to me like you believe users should use spreadsheets the way you think spreadsheets should be used. I doubt you can imagine some of the calculations spreadsheets are actually used for or why. As I explained about the MOD() bug: if a word processor or presentation graphic (eyewash) program has a bug, lives and money aren't at risk. Spreadsheets are different: the ceilings over your and you family's heads, and the bridges you drive over, may well have been designed using spreadsheets. Random wrong answers are worse than crashes; they're unacceptable. -- 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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.documentfoundation.org/show_bug.cgi?id=87386 --- Comment #11 from GerardF gerard.farg...@orange.fr --- (In reply to klsu from comment #0) Cell C13 D13 contain general number 24.09 and text 24.09.13, respectively (European dates dd.mm and dd.mm.yy in a .CSV opened on a US machine). Formula to convert the first dd.mm and the second year to a date would be =DATE(2000+VALUE(RIGHT(D13,2)),MOD(C13,1)*100,INT(C13)), however, for this particular date the formula returns 08/24/13 rather than 09/24/13. Note that: =DATE(2000+VALUE(RIGHT(D13,2)),0.09*100,INT(C13)) works =DATE(2000+VALUE(RIGHT(D13,2)),C13*100-INT(C13)*100,INT(C13)) works =DATE(2000+VALUE(RIGHT(D13,2)),(C13-INT(C13))*100,INT(C13)) does not =DATE(2000+VALUE(RIGHT(D13,2)),MOD(C13,1)*100,INT(C13)) does not A similar problem occurs with number 7.05 and text 07.05.13 and others, but not all. Instead of using DATE, VALUE, RIGHT, INT, MOD and other hundreds of Functions, just open correctly the .csv file and you will have true dates and don't need any functions. -- 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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.documentfoundation.org/show_bug.cgi?id=87386 k...@cox.net changed: What|Removed |Added Status|RESOLVED|REOPENED Resolution|NOTABUG |--- --- Comment #10 from k...@cox.net --- Some people need to learn that computers and software are supposed to work for people. People do not work for computers. Telling someone they have to do what the computer wants doesn't resolve the problem (in this case, random wrong answers). So, not a proper use of general numbers, will never work reliably and Floating point numbers just don't work that way don't change the fact that this is a bug. The whole point of GUIs is what you see is what you get. If someone sees 0.09, it is reasonable for them to expect it to work in calculations as 0.09, and it normally will. Where it won't is with any function that truncates digits without regard to their significance, so for those types of functions, the software must account for the fact that computers must work with more digits than they show, so they can display the correct answer. This is not a problem with number types. It is a problem with algorithms used for functions. As has been pointed out in the thread of a related bug with the MOD() function, random wrong answers due to inadequate or improper handling of significant digits is not acceptable to anyone in a job where life or property could be harmed or lost by such an error. That means engineers, logisticians, doctors and accountants should not use the MOD() or DATE() functions in LibreOffice. The fact that this discussion is ongoing tells me spreadsheet functions haven't been standardized, and that's too bad. I'm stuck with MS Excel for the foreseeable future, since the open source community apparently doesn't understand user requirements for spreadsheets. I got involved in bug reporting, thinking the community might want to know what users need. Now I'm not sure they do. Someone who wants to get away from Windows won't find a spreadsheet for Linux that can be depended on for all the functions I use. But LibreOffice comes with those functions, and most people don't know they can give wrong answers. You could bury a warning with descriptions of the MOD() and DATE() functions saying they shouldn't be used for important work, but what engineer or accountant needs to read the descriptions of MOD() and DATE(). If the function doesn't work reliably, it shouldn't be implemented. DATE() and MOD() worked as they should for the more than 20 years I've used them in Excel, so when someone tells me one will never work reliably, it sounds like they think the current algorithm is the only one possible. I can think of many ways to work around the problem, but why would I unless I know the functions don't work properly? Most LO Calc users don't know, and if they did, I suspect they would not be happy if they use the DATE() function. If I were programming a DATE() function that expected only integers as input for YEAR, MONTH, or DAY, I would make very sure that every passed value was appropriately rounded BEFORE being used in the function. (However, I probably wouldn't design the DATE() function to expect only integers, because dates are stored as floating point days that can include the information returned by TODAY() or NOW(), so the DATE() function should allow addition of decimal days to the value returned by NOW() or any other date/time.) -- 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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.documentfoundation.org/show_bug.cgi?id=87386 Matthew Francis fdb...@neosheffield.co.uk changed: What|Removed |Added Status|REOPENED|RESOLVED CC||fdb...@neosheffield.co.uk Resolution|--- |NOTABUG --- Comment #9 from Matthew Francis fdb...@neosheffield.co.uk --- Unfortunately this is simply not a proper use of general numbers, and will never work reliably. Floating point numbers just don't work that way. Format the columns as dates, and use the DAY(), MONTH() and YEAR() functions as appropriate - or if you must, format all the columns as text so that the text based dissection you are attempting has a chance of succeeding. Closing this bug again. -- 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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.documentfoundation.org/show_bug.cgi?id=87386 Matthew Francis fdb...@neosheffield.co.uk changed: What|Removed |Added Status|REOPENED|RESOLVED Resolution|--- |NOTABUG --- Comment #12 from Matthew Francis fdb...@neosheffield.co.uk --- To repeat, as a member of LibreOffice QA, I am closing this bug. Reopening it will not change the outcome. -- 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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.freedesktop.org/show_bug.cgi?id=87386 --- Comment #8 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 of them; only Excel does this correctly. A one month error in a date can be the difference between Short-Term and Long-Term. If you believe that the I.R.S. doesn't care about that difference or would accept the excuse I used OfficeLibre/Open Office/Gnumeric/Calligra Sheets, which has a bug in the DATE() function, dream on... 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 87386] Calculation inaccuracy converting calculated numbers to dates and integers.
https://bugs.freedesktop.org/show_bug.cgi?id=87386 k...@cox.net changed: What|Removed |Added Status|RESOLVED|REOPENED Resolution|DUPLICATE |--- Summary|Calculation inaccuracy and |Calculation inaccuracy |inconsistency converting|converting calculated |numbers and text to dates. |numbers to dates and ||integers. --- Comment #7 from k...@cox.net --- (In reply to m.a.riosv from comment #6) Please take a look to this bug report just about MOD() function: https://bugs.freedesktop.org/show_bug.cgi?id=50299 Specially Kohei comment #4. Resolved as duplicate, please if you are not agree reopen it. *** This bug has been marked as a duplicate of bug 50299 *** Thank you, I've already reopened 50299, with comments 20, 21 22, and an attachment showing how the problem is not only a problem, but is worse than many of the other commenters seem to think (in LO Calc 4.4.0 Beta2 Dev Daily, MOD(230,10)=0, MOD(2.3*1000,100)=100). There is clearly an error with how LO Calc calculates the modulo. The problem is not so extreme in Excel. As long as hardware has been around, I doubt Intel and Motorola have been just ignoring postgrad work on fast modulus algorythms (e.g., http://www.dtic.mil/dtic/tr/fulltext/u2/a547555.pdf), so I question hardware as the cause. I am trying to figure out what to tell someone who is familiar with the MOD() function code that will help him/her fix it. So far, what I sure of is that the MOD() function is not rounding the dividend to the correct number of significant digits before doing the math. Real example: =MOD(ROUND(36.30002,9)*100,10) gives the result 10, which is wrong. =MOD(ROUND(36.3,9)*100,10) gives the same result. =MOD(36.80003*100,9),10) gives 2.45563569478691E-011, which you might look better...until you see that =MOD(36.3*100,10) gives 10 However, that's not the problem that caused me to create this bug report, so I am reopening it. Here, the problem appears to be that DATE() (and INT()) do the same thing; not rounding parameters passed as real numbers to the correct number of significant digits before executing the function. Passing a constant real number, doesn't cause so much trouble, but the whole point of functions is to allow working with calculated values, and those tend to be real until you do something special to them to convert them. The program is supposed to do that when it's needed, because it doesn't always display what it's actual result is, so the user has no way of knowing the general number 9 that's displayed is actually 8.999. MOD(24.09,1) should equal 0.09, although LO Calc displays 0.09 it is using 0.0899, so 0.09*100 displays 9 but uses 8.99. Since that 15 digit number is not correct, any function that expects an integer needs to make sure it's rounded correctly. But multiple calculations tend to adversely affect accuracy in more digits, so after 1000 additions, a value (real example) displayed as 100 may be represented internally as 99.99986, so when dealing with significant digits, rounding must be to digits, not to decimal points, and rounding a 15 digit number to 15 places will eventually give a wrong answer. To use 99.99986 in an integer or date function it must be rounded to 11 decimal places or 13 digits. I've done engineering calculations that added many thousands and were still accurate enough so rounding to 13 digits (not decimal places) was sufficient. If you want to see what number you're actually using, just use the formula =[cell reference]. -- 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
