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

classic Classic list List threaded Threaded
8 messages Options
Terrence Enger Terrence Enger
Reply | Threaded
Open this post in threaded view
|

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

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.


_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/
Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"


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.

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.

Jay

--
Jay Lozier
[hidden email]

_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/
Terrence Enger Terrence Enger
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"

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,


_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/
Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"


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
[hidden email]

_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/
m.a.riosv m.a.riosv
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"

Hi,

maybe could be of interest comments #4 and #6 of Kohei in bug

MOD shows not existing small remainder with calculated Dividend

Miguel Ángel.
Jean-Baptiste Faure Jean-Baptiste Faure
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"

Hi,

Le 30/06/2014 02:13, m.a.riosv a écrit :
> Hi,
>
> maybe could be of interest comments #4 and #6 of Kohei in bug
>
> MOD shows not existing small remainder with calculated Dividend
> <https://bugs.freedesktop.org/show_bug.cgi?id=50299#c4>  

Indeed, but in some particular cases, rounding errors may have
disastrous effects.
For example, again on MOD() function:
https://bugs.freedesktop.org/show_bug.cgi?id=80732

Rounding errors are acceptable if they remain (very) small. If they are
not, something should be done.

Another very clear case is the following:
- type 0.1 in A1
- in A2 type =A1*11-1
- propagate the formula to A30
Enjoy ;-)

Best regards.
JBF

--
Seuls des formats ouverts peuvent assurer la pérennité de vos documents.
_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"

Hi All,
On 06/30/2014 01:49 PM, Jean-Baptiste Faure wrote:

> Hi,
>
> Le 30/06/2014 02:13, m.a.riosv a écrit :
>> Hi,
>>
>> maybe could be of interest comments #4 and #6 of Kohei in bug
>>
>> MOD shows not existing small remainder with calculated Dividend
>> <https://bugs.freedesktop.org/show_bug.cgi?id=50299#c4>  
> Indeed, but in some particular cases, rounding errors may have
> disastrous effects.
> For example, again on MOD() function:
> https://bugs.freedesktop.org/show_bug.cgi?id=80732
>
> Rounding errors are acceptable if they remain (very) small. If they are
> not, something should be done.
>
> Another very clear case is the following:
> - type 0.1 in A1
> - in A2 type =A1*11-1
> - propagate the formula to A30
> Enjoy ;-)
This is indeed well known and Kohei has explained why it happens (quite
a few times). The logic is beyond my comprehension but I trust his
judgment 110% and he has said it is really unfixable. I believe we have
closed at least 5 similar bugs in the past year.


Best,
Joel
_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/
Jean-Baptiste Faure Jean-Baptiste Faure
Reply | Threaded
Open this post in threaded view
|

Re: fdo#80638 "Incorrect SUM with decimal numbers"

Hi,

Le 01/07/2014 03:59, Joel Madero a écrit :
[...]
> This is indeed well known and Kohei has explained why it happens (quite
> a few times). The logic is beyond my comprehension but I trust his
> judgment 110% and he has said it is really unfixable. I believe we have
> closed at least 5 similar bugs in the past year.

Yes, I share the judgment of Kohei and Markus that the rounding errors
problem can't be solved for all computations without losing performance.
Infinite precision is too expensive.
Current behavior is not problematic if we keep rounding errors small. It
is not always the case and we should try to avoid the cases where
rounding errors gives final errors that are not small. That is the
challenge of Numerical Analysis, finding correct results with false
computations. ;-)

In comment #2 of https://bugs.freedesktop.org/show_bug.cgi?id=80732 I
showed that it is possible to make better computations of MOD() and
QUOTIENT() functions. At least in the cases reported here.

Best regards.
JBF

--
Seuls des formats ouverts peuvent assurer la pérennité de vos documents.
_______________________________________________
List Name: Libreoffice-qa mailing list
Mail address: [hidden email]
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/