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.
Created attachment 88997 [details] test for COMBIN
(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.
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.
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
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.
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.
OK, I accept the lesson on how to compute COMBIN...
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 ;-)
> 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.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: *Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1 or later) https://www.libreoffice.org/download/ *If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior *If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT *Update the version field *Reply via email (please reply directly on the bug tracker) *Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-04-18
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.
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.
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.