Created attachment 114350 [details] File with the bug (?) In the attached file I have a problem in cell F11. If I enter the formula = f4 + f5 + f6 + f7 + f8 + f9 + f10 I get 0. If I enter the function = sum (f4: f10) the result is different from 0. If I open the same file with Excel everything works fine . Regards Andrea Tessadori
On pc Debian x86-64 with master sources updated yesterday, I could reproduce this. Could you give your LO version? (meanwhile, I put master)
Looks that using + uses a different way to round off than sum(). For example put the option: calculate / 'precision as shown' on (with the test file). With '+' the outcome is 0.1. With sum() the outcome here is 1.00E-001, which is actually the same, only differently shown. So bug or intentional ???
Sorry for my poor English. Now I'm using 4.4.1.2 but before I used a previous version (maybe 4.1) with the same problem. I tried also with Open Office 4.1.1 and I have not encountered the problem. Everything in windows 7 and 8.1 environment. In the calculations of the sheet there is nothing to be rounded. The only division is in cell E12 where there is an "=average()" function (=media()) but the result is accurate to two decimal places. Even if I write by hand 3.85 in E12, I get a result different from 0 in F11. That is wrong! In my opinion there is a problem in the function = sum () that finds decimals that do not exist !!! Excel and OpenOffice not have this problem. Regards Andrea Andrea Tessadori
The problem is caused by the fact that decimal values cannot be translated _exactly_ to binary values. Even though the diffences are extremely small, with SUM they sometimes show and are annoying. I haven't found out yet why the =..+..+.. does not have this problem and SUM with the same values does, still working on that. Unfortunately, I haven't a solution yet.
Forcing recalculation to override the cached value in the file gives a result of 0.00 until source-hash-61db96daa87754af24355d7ac94ee0305f22ff87 in bibisect 43all (* recalculation by editing the cell - F9 doesn't seem to affect this for some reason) At a quick scan this is most likely a result of the below commit commit 9d207f6fc479bf96db9fcde214f15f2ffb045001 Author: Kohei Yoshida <kohei.yoshida@gmail.com> Date: Tue Jul 2 00:51:10 2013 -0400 Re-implement SUM function to make use of new cell storage. And fix a bug in my new COUNT function, where I forgot to tally numeric formula cells. Change-Id: I52d26be3e48f646f656821066e23594d52f78c6d and/or this one which followed it commit d61233a388284383a939e2f26a0e25b9ad243898 Author: Kohei Yoshida <kohei.yoshida@gmail.com> Date: Tue Jul 2 16:02:59 2013 -0400 Move this code from the column code back into the interpreter code. Change-Id: I7830cdf3f09ed7b6ae6221212bfb84abcdeac523 Still not sure if this is a genuine bug or just an expected consequence of floating point arithmetic. However, as it used to give an exact result, let's call this a regression until a Calc developer decides otherwise.
(In reply to Matthew Francis from comment #5) > Still not sure if this is a genuine bug or just an expected consequence of > floating point arithmetic. However, as it used to give an exact result, > let's call this a regression until a Calc developer decides otherwise. Also, function AGGREGATE, which still uses the 'old' code (the code that SUM used before Kohei's changes) produces an exact result. @Kohei: do you have time to look into this? Otherwise I will see if I can find something.
I'm removing the regression keyword here. This is not really a bug. It is the expected behavior for floating point arithmetics. Any change in the algorithm will result in other documents showing a loss of precision. Normally we close these bugs directly as there is nothing we can do. I had a quick look at the algorithm and while there are ways to improve the precision they will be much slower.
The problem is caused side effects of improvements carried out by Kohei (see comment #5). I am working on a fix using the old 'workaround' to prevent precision problems (which are unavoidable) in the code written by Kohei. Testing and finalising the fix will take some time yet.
@Winfried: PLease be careful as this will introduce new problems with different numbers.
(In reply to Markus Mohrhard from comment #9) > @Winfried: PLease be careful as this will introduce new problems with > different numbers. @Markus: I will, see gerrit 15362. It is more restoring to the pre-column based situation with respect to the precision errors. It won't fix all precision errors, only some like this bug report. And Eike will have a look at it, too.
*** Bug 92962 has been marked as a duplicate of this bug. ***
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]
As said in various comments, the underlying cause is the floating point arithmetics and cannot be remedied. It is possible to 'fix' the example in the attachment, but not all instances of calculations with floating point values, so 'fixing' for one or two specific cases has no real use.