Thank you Christopher M. Penalver for the guidance towards a more streamlined problem report. Row 5 in the posted spreadsheet, LibreOfficeRoundingIssues.ods, shows the precise errors described in my initial report here. The cells in that row contain, successively, x=50, y=power(2,x)+1, round(y,0)-y, roundup(y,0)-y, rounddown(y,0)-y, trunc(y,0)-y, ceiling(y,1)-y, floor(y,1)-1, int(y)-y, even(y)-y and odd(y)-y. The expected result is a 0 in the cells for functions round ... int, a 1 for even(y)-y (because y is odd), and a 0 for odd(y)-y. As is highlighted in yellow in the sheet the actual result is different in each case except for the first function, plain round.
A minimal set of instructions to reproduce the core of the problem follows. Open a new blank spreadsheet. In the A1 cell enter "=power(2,50)+1" (without the quotes) and in the B1 cell enter "=rounddown(a1,0)-a1" (without the quotes). Expected result: B1=0. Actual result: B1=5. I take it as understood that when an integer is rounded to integer then the result should be the same integer. The actual result in LibreOffice is a different integer, therefore it is a wrong result. I am asked to explain the significance of the error and to address possible negatives in fixing it. To be clear, the relative magnitude of the error is tiny and when viewed purely as a numerical error it is insignificant in any engineering or financial context. The risk of this kind of error in applied code is rather that it breaks assumptions. A person may write a spreadsheet program and take for granted that for positive argument y, rounddown(y,0) can never exceed y. The present report shows that this entirely justifiable assumption is broken in LibreOffice Calc. That risk is a risk to a third party (user of LibreOffice) and not directly to the LO project team. The significance of this error directly to LibreOffice is, I think, primarily a risk to the reputation of the product. There may be cases where one wants to follow Excel in a questionable specification of a spreadsheets function, because users rely on that specification. However, I cannot conceive of any spreadsheet design that would in any way rely on erroneous behavior of the rounding functions for large argument. Therefore I think that the Calc team could correct this error without concern over breaking any compatibility with Excel. -- You received this bug notification because you are a member of Desktop Packages, which is subscribed to libreoffice in Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument Status in “libreoffice” package in Ubuntu: Incomplete Bug description: I am using Ubuntu 12.04.3 LTS and within that LibreOffice 3.5.7.2, Build ID: 350m1(Build:2). This report concerns LibreOffice Calc; functions roundup, rounddown, trunc, ceiling, floor, int, even and odd. Let y=power(2,50)+1 and please note that all integers up to power(2,53) are exactly representable. Therefore I expect that rounding y to integer will return y exactly, for any of the functions roundup, rounddown, trunc, ceiling, floor and int. I also expect that even(y)=y+1 and odd(y)=y. Actual results: functions roundup(y,0), rounddown(y,0), trunc(y,0), ceiling(y,1), floor(y,1) and int(y) all return y+5. Moreover, even(y)=y and odd(y)=y+6 according to Calc. (Note that the second argument to roundup, rounddown and trunc indicates number of places behind the decimal symbol and the second argument to ceiling and floor indicates the unit.) There are similar problems with these rounding functions when rounding to some number (>0) of places behind the decimal symbol. Example: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+attachment/3942054/+files/LibreOfficeRoundingIssues.ods --- ApportVersion: 2.0.1-0ubuntu17.6 Architecture: i386 DistroRelease: Ubuntu 12.04 InstallationMedia: Ubuntu 12.04.3 LTS "Precise Pangolin" - Release i386 (20130820.1) MarkForUpload: True Package: libreoffice 1:3.5.7-0ubuntu5 PackageArchitecture: i386 ProcEnviron: TERM=xterm PATH=(custom, no user) LANG=en_US.UTF-8 SHELL=/bin/bash ProcVersionSignature: Ubuntu 3.8.0-35.50~precise1-generic 3.8.13.13 Tags: precise running-unity Uname: Linux 3.8.0-35-generic i686 UpgradeStatus: No upgrade log present (probably fresh install) UserGroups: adm cdrom dip lpadmin plugdev sambashare sudo To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- Mailing list: https://launchpad.net/~desktop-packages Post to : desktop-packages@lists.launchpad.net Unsubscribe : https://launchpad.net/~desktop-packages More help : https://help.launchpad.net/ListHelp
