(In reply to comment #3) > The Wikipedia reference is to be appreciated because the relevant IEEE-754 > standard (1985 original or 2008 revision) is behind a subscription barrier. > I will refer to the standard nevertheless, and for present purposes Std > 754-1985 suffices. Section 5, Operations, is the relevant part. I quote: > > "All conforming implementations of this standard shall provide operations to > add, subtract, multiply, divide, extract the square root, find the > remainder, round to integer in floating-point format, convert between > different floating-point formats, convert between floating-point and integer > formats, convert binary <---> decimal, and compare." > > Subsection 5.5 expands on the specification of rounding to integer. For my > posted example the situation is simple: the input value is an exactly > representable integer, y=power(2,50)+1. Following Microsoft Excel, > LibreOffice Calc offers a bunch of functions to perform rounding to integer; > they include round(.,0), roundup(.,0), rounddown(.,0), trunc(.,0), > ceiling(.,1), floor(.,1) and int(.), where I use the "." symbol to denote > the free argument. > > For integer argument, as in the present case, there can be no ambiguity > about what is the result of rounding to integer; the mathematical result > value is the same as the input value. Moreover, given that the input value > is a representable IEEE-754 number likewise this result value is exactly > representable; it is the same value. It is an error in Microsoft Excel and > in LibreOffice Calc that a different result is returned. (The present > situation is very different from the familiar situation of adding and > subtracting decimal numbers with two digits behind the decimal point and > finding a result that differs from the correct mathematical result by some > value much less than 0.01.) > > I note that the tests of this kind of arithmetic can be confusing, because > in fact Microsoft Excel 1997 (and later) and LibreOffice Calc deliberately > violate IEEE Std 754-1985 for arithmetic very close to a cancellation > threshold [1]. I quote: > > "Excel 97, however, introduced an optimization that attempts to correct for > this problem. Should an addition or subtraction operation result in a value > at or very close to zero, Excel 97 and later will compensate for any error > introduced as a result of converting an operand to and from binary." > > It is not very clearly expressed, and "Optimization" would not be my choice > of words. Anyway, this concerns plain arithmetic and it muddles the tests, > but it does not affect the present issue with the rounding functions. > > I note that my report received the lowest ranking in both columns for > importance; that is not my assessment. The numerical error is tiny, sure, > but it may break logic in a code if the result of rounddown(.,0) applied to > a positive number can actually return a larger number, and I think that the > reputational cost is not to be trivialized. One may recall the Pentium > division bug. > > [1] Floating-point arithmetic may give inaccurate results in Excel - Example > When a Value Reaches Zero > http://support.microsoft.com/kb/78113
We implement exactly the same optimization technique to work around some of the floating point accuracy problems. This is part of our core code and will not be changed. I'm sorry there is nothing to discuss. This is a technical decision that you have to live with if you want to use Libreoffice Calc. Please don't reopen this bug as I explained already that this will never be changed. -- 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: [Upstream] Wrong results from rounding functions for large argument Status in LibreOffice Productivity Suite: Won't Fix Status in “libreoffice” package in Ubuntu: Won't Fix Bug description: 1) lsb_release -rd Description: Ubuntu Trusty Tahr (development branch) Release: 14.04 2) apt-cache policy libreoffice-calc libreoffice-calc: Installed: 1:4.1.3-0ubuntu3 Candidate: 1:4.1.3-0ubuntu3 Version table: *** 1:4.1.3-0ubuntu3 0 500 http://us.archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages 100 /var/lib/dpkg/status I am using Ubuntu 12.04.3 LTS and within that LibreOffice 3.5.7.2, Build ID: 350m1(Build:2) but this is also reproducible in LO Trunk 4.3.0.0.alpha0+ on Windows Vista: What is expected to happen at a terminal: cd ~/Desktop && wget https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+attachment/3942054/+files/LibreOfficeRoundingIssues.ods && localc --nologo LibreOfficeRoundingIssues Is that for cell D5 is it 0. What happens instead is that it is 5. This would be an issue with Calc numerical precision, as the actual outcome of 5 is also the same with Excel. WORKAROUND: Use gnumeric: apt-cache policy gnumeric gnumeric: Installed: 1.12.9-1 Candidate: 1.12.9-1 Version table: *** 1.12.9-1 0 500 http://us.archive.ubuntu.com/ubuntu/ trusty/universe amd64 Packages 100 /var/lib/dpkg/status --- 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/df-libreoffice/+bug/1261048/+subscriptions -- Mailing list: https://launchpad.net/~desktop-packages Post to : [email protected] Unsubscribe : https://launchpad.net/~desktop-packages More help : https://help.launchpad.net/ListHelp
