[Libreoffice-qa] fdo#80638 "Incorrect SUM with decimal numbers"

[Jay Lozier]

On 06/29/2014 04:16 PM, Terrence Enger wrote:
> On Sun, 2014-06-29 at 14:11 -0400, Jay Lozier wrote:
>> On 06/29/2014 12:53 PM, Terrence Enger wrote:
>>> Hi, all.
>>>
>>> I have just, for the first time that I remember, changed a confirmed bug
>>> report to a request for enhancement.  I would like confirmation or
>>> correction of my judgement.
>>>
>>> The report is fdo#80638 "Incorrect SUM with decimal numbers"
>>> <https://bugs.freedesktop.org/show_bug.cgi?id=80638>.  My thought is
>>> that Calc simply does not do decimal numbers.
>>>
>>> Thanks for your attention,
>>> Terry.
>> Hi,
>>
>> This is not an LO bug. I get the exact same result with a Python script,
>> a Ruby script, and Calligra Sheets.
>>
>> It is a problem with internal representation of decimal numbers in any
>> computer system. This is a well known problem in scientific
>> computing/numerical analysis.
> I think I hear a distant voice, echoing down the decades, sayting
> "truncation error".
>
> <rant>
>    The PC world tends to accept this problem as inevitable.  However, the
>    larger IBM systems going back to System/360 and other systems
>    inspired by it have offered decimal arithmetic.  This, too, has its
>    "funnies", but the problems tend to manifest themselves in
>    high-order digits, and so are often less subtle.
> </rant>
>> Also, reordering the addition produced different precisions. I think the
>> correct numerical analysis term for this is precision referring to how
>> closely the actual value is to the true value.
>>
>> I would close this report with the note this is not a problem specific
>> to LO or any other spreadsheet. All spreadsheets will produce similar
>> results depending on the specifics of how real numbers are handled by
>> the underlying OS/CPU.
> The computers handle real numbers well enough they can fool the unwary
> into thinking that the computers' real numbers are like a
> mathematician's real numbers.  It just ain't so, of course: the
> computer's real numbers are a finite subset of the mathematician's
> rational numbers.
>
> I cannot imagine what a good fix for the problem would be.  And to
> change the result of a calculation, even a wrong result, is likely to
> break somebody's workflow.
>
> One possibility is to introduce a new function, call it betterSum
> perhaps, which accumulates the addends in order of increasing absolute
> value.  This would not change the behaviour of existing spreadsheets.
> I do not expect that this would be worth the effort.
>
> So, yes, I think I shall close the report NOTABUG.  (But you can tell
> that I am greatly tempted by NOTOURBUG, can't you?)
>
>> Jay
>>
>>
> Thank you, Jay, for helping me to this decision.
>
> Terry,
>
Hi,

NOTOURBUG LOL!

I read a couple of books on numerical methods in the mid 80's and they 
both discussed this problem as adding to the natural measurement errors 
in one's data. The point both made was it could be minimized but never 
eliminated. Because it could not be eliminated there could be a 
situation where this error blew up and caused bogus results to be generated.

What I remember of the potential fixes is they are not something that a 
spreadsheet user would  typically do. One is to encode the data as text, 
then programmatically convert it into integers which works fairly well 
with currency. The spreadsheet equivalent is to enter all the data as 
integers. Division and possibly multiplication will sometimes create 
real numbers but the precision problem is typically small enough that it 
should not cause problems. Another was to move the decimal point to 
eliminate leading zeros. The idea is if some of the data is 0.00xy 
meters and some is a.bd0 meters it should enter as millimeters x.y mm 
and abd0 mm instead. This would work on a spreadsheet.

-- 
Jay Lozier
jslozier at gmail.com