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
jsloz...@gmail.com
_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/
