Bug 71459 - No easy way to subtract two numbers (without snap-to-zero)
Summary: No easy way to subtract two numbers (without snap-to-zero)
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:5.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2013-11-10 19:37 UTC by M Welinder
Modified: 2016-08-01 13:39 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
test for COMBIN (17.44 KB, image/png)
2013-11-11 08:35 UTC, GerardF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description M Welinder 2013-11-10 19:37:02 UTC
A1=COMBIN(15,9)
A2=5005
A3=A1-A2

This produces a zero in A3, which would be correct if COMBIN actually
worked and produced 5005.  COMBIN doesn't, so 0 is wrong.

A4=A1+1-A2-1
--> 9.09494701772928E-013

I understand what is happening in A3: "-" in LO does not perform IEEE 754
subtraction.  It performs what can be described as snap_to_zero(A1 minus A2)
where that "minus" is IEEE 754 subtraction.  I even understand the political
reasons why snap_to_zero is in place.

What I am lacking is the ability to just subtract (compare, etc.) two
numbers, i.e., answer the question "does A1 contain 5005?"  Maybe that
is a RAWSUBTRACT function, maybe something else.  How do you test
whether functions like COMBIN actually work without that ability?

FWIW, in Excel adding a parenthesis around the subtraction is enough to
get raw subtraction because the snap-to-zero behaviour is only used for
subtraction at the top-most level.  (I think -- it might be more complicated
than that.)

Oh, and someone needs to fix COMBIN.
Comment 1 GerardF 2013-11-11 08:35:10 UTC
Created attachment 88997 [details]
test for COMBIN
Comment 2 GerardF 2013-11-11 08:37:19 UTC
(In reply to comment #0)
> A1=COMBIN(15,9)
> A2=5005
> A3=A1-A2
> 
> This produces a zero in A3, which would be correct if COMBIN actually
> worked and produced 5005.  COMBIN doesn't, so 0 is wrong.

COMBIN(15;9) works for me with 4.1.3 and 4.2.0 alpha.
See above attached PNG.
Including some substract operations.
Comment 3 M Welinder 2013-11-11 13:57:57 UTC
The screenshot in attachment 88997 [details] clearly shows the COMBIN problem in A4.
If the result in A1 really was 5005 then A4 would be zero.

The actual value in A1 is something like 5005+9e-13 and cells A3, A5,
and A6 show LO trying hard to conceal it.

But fixing COMBIN isn't the point here.  The ability to access and
subtract the actual number in cells A1 and A2 is.
Comment 4 Dominique Boutry 2013-11-18 09:01:41 UTC
The spreadsheets calculate and store numbers with the greater precision offered by the computer technology :
- Floating mantissa 52 bits precise to 1.0E-16 (approx : 52/10 * 3 = ~ 16)
- Other technics (probable : 64-bit integers acting as numerator and denominator of a fractional number) 
- Perhaps still others, at the discretion of the implementers , as long as it allows a greater precision.

Finite precision is observable in the following expression :
= 100000000000000000000 + 1 - 100000000000000000000 , which is evaluated as 0 , not 1.

Integer storage of integers up to 10000000000000000 is garanteed accurate. But there is no warranty that intermediate results don't show "integer overflows", so intuitive use of integers is rapidly inappropriate.

The COMBIN function ( a; b ​​) is difficult to implement :
- Implement it as FACTDOUBLE = (a) / FACTDOUBLE (b) is too restrictive given the loss of accuracy of FACTDOUBLE from 27,
- Move to floating numbers is not surprising , and introduce the loss of relative accuracy of 1.0E-16 on input calculation arguments, totaling here 1.0E-13 on output calculation :
COMBIN (15;9) + 1 - 5005 evaluates to 1.00000000000091000000

It belongs to the application developer EXCEL / LibO Calc developer to design a "digitally correct" application and to avoid sequences that make significant some bits located in the error area at mantissa end. It is impossible to track back the evolution of the numeric error along the calculations.
Programmers take into account that for 40 years in calculation which led us to the moon ;-)
Best solution here : = ROUND ( COMBIN ( 15;9 ) ) + 1 - 5005
Comment 5 M Welinder 2013-11-19 02:01:02 UTC
Guys, stop defending what LO currently does.  It's just plain silly.
Take a look at this...

http://cgit.freedesktop.org/libreoffice/core/tree/sc/source/core/tool/interpr3.cxx#n448

...from which we learn:

1. Enter =COMBIN(1e15,1e15-1) and LO will hang for ages.  For slightly
   higher numbers "k--;" won't change the value of k, so that will be
   an infinite loop.

2. Whoever wrote this didn't know the basics of combinatorics.  Notably
   COMBIN(n,k) = COMBIN(n,n-k).  I.e, for efficiency, replace k by n-k
   if the latter is smaller.

3. The code is doing the divisions in the wrong order.  Here's what it does
   for COMBIN(15,6):

       15*14*13*12*11*10
       -----------------
        6* 5* 4* 3* 2* 1

   (as (15/6) * (14/5) * ... * (10/1) -- note the parentheses.  Some of
   these fractions have rounding errors.)  Other than the wrong order of
   operations, that's not too bad way to compute this.  However, it
   should have been doing

       15*14*13*12*11*10
       -----------------
        1* 2* 3* 4* 5* 6

   (as 15/1*14/2*13/3...*10/6 -- note the lack of parentheses, so the
   multiplication comes before the division in each step.)

   Doing things this way means that all numbers stay integers.  There
   will not be any rounding errors unless the final answer is (much)
   more than 2^53.  In the C(15,6)=C(15,9) case, all numbers would fit
   comfortably in a 16-bit integer.

   To prevent a hang for C(2^53,2^52), one should check for overflow.


But again, COMBIN is a sideshow here.  The real issue is the lack of on
honest subtraction.
Comment 6 Markus Mohrhard 2013-11-19 07:15:55 UTC
Please leave out of this discussion.

There are good reasons for our floating point behavior and I don't want to get the 100th time into the same discussion.
Comment 7 Dominique Boutry 2013-11-19 08:20:32 UTC
OK, I accept the lesson on how to compute COMBIN...
Comment 8 Michael Meeks 2013-11-19 13:44:41 UTC
So - I'm sure Morten knows what he is doing better than I do =) I'll confirm the bug.

Morten - just to get a handle on what you're saying it -sounds- like you're concern is that you (somewhat unusually) want to see the floating point artifacts in cells, whereas the majority of the world don't ;-) is that a fair summary ? [ and you'd like some way to turn that off ].

Of course, if Excel provides a way to avoid that, it's interesting in itself; my hope would be that this would be a number formatting issue and there would be no underlying loss of precision.

Patches to improve COMBIN much appreciated too ;-)
Comment 9 M Welinder 2013-11-19 19:46:18 UTC
> Morten - just to get a handle on what you're saying it -sounds- like you're
> concern is that you (somewhat unusually) want to see the floating point
> artifacts in cells, whereas the majority of the world don't ;-) is that a
> fair summary ? [ and you'd like some way to turn that off ].

Very close.  I don't need it all over, just in one single place.
A function RAWSUBTRACT might be that place.
Comment 10 QA Administrators 2015-04-19 03:22:13 UTC Comment hidden (obsolete)
Comment 11 mwelinder 2015-04-19 20:34:04 UTC
Latest I have access to is LibreOffice 4.2.7.2 420m0(Build:2)

The main bug -- lack of raw way to subtract two numbers -- still occurs.
There is next to no chance that it has been fixed between 4.2.7.2 and HEAD
by someone unaware of this bug.

The sideshow -- COMBIN -- still has all the issues in my version.  It is,
however, immaterial for the purpose of this bug.
Comment 12 Commit Notification 2015-12-17 14:17:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=db1e34aecd4290623a74b9bbeb602e072b1a49ec

add RAWSUBTRACT spreadsheet function, tdf#71459

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2015-12-17 14:17:16 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4eea9f214682008052424479a4b1f8cf90a79132

handle varargs with first required and subsequent optional, tdf#71459 related

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.