Comment 5 for bug 1261048

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.