[Upstream] Wrong results from rounding functions for large argument

Created attachment 91706
Calc spreadsheet to demonstrate rounding errors for large argument values

This report concerns LibreOffice Calc; functions roundup, rounddown, trunc, ceiling, floor, int, even and odd.

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.

More generally, let y=power(2,50)+1. 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.

The attached spreadsheet illustrates the issue. I have highlighted in yellow the cells where the result is different from what is expected. Row 5 shows the precise results described here; other rows show similar calculations for values y=power(2,x)+1 for other values of x.

Please read about floating point arithmetics.

http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

Markus Mohrhard, thank you for your comment. While the prior work done in this area is appreciated, in that it replicates the output of Excel (yes, I already know LibreOffice isn't Excel), I think closing this as a Won't Fix is premature, especially predicated on a post from Wikipedia.

Despite this, venerable Gnumeric ( https://projects.gnome.org/gnumeric/ ) does in fact provide a more accurate result than LibreOffice or Excel in this instance:
lsb_release -rd
Description: Ubuntu Trusty Tahr (development branch)
Release: 14.04

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

I think this should be discussed much further technically before making such a judgement.

Thank you for your understanding.

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

(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...

<<Please don't reopen this bug...>> I would not dream of it. Let me only record a possible misunderstanding. My report is not about that Excel "optimization" by which near-cancellations are replaced by exact cancellations; I mentioned that optimization only peripherally because it affects the diagnostics. Instead, my report concerns the arithmetic of the Excel and LibreOffice Calc rounding functions, by which "round to integer" of an exactly representable (IEEE-754) integer may return a different integer. In a way it is the opposite of the mentioned optimization. It replaces exact equality (which would be the correct mathematical result) by only approximate equality. It is indeed a tiny error, only about 6 bits in the mantissa in the worst case. It is just barely large enough to exceed the 5 bits of error that would be masked by that Excel optimization.

WORKSFORME with Version: 7.0.3.1 (x64)
Build ID: d7547858d014d4cf69878db179d326fc3483e082
CPU threads: 12; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL