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

[Terrence Enger]

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,