Created attachment 113375 [details] Example of calculation approach I have spreadsheets, where sum() function is used to calculate, when land use conversion or other measures reach maximum. The example is in attachment. Before the 4.4.1.1 (probably, also in 4.4.0.3) I didn't have any problems with this approach. An example of reduction of carbon stock in soil follows in attachment (the problematic formulas are in range A40:BK102). Today I recognize that sum() function is not working well in all cases. An example of the problem: I got value "1904" in cell range B41:B61, which means that sum(B41:B101) is > B102. Normally according to the formula, like in previous calc versions I would get value "1904" in cell range B41:B60 and sum(B41:B101) would be = B102. The input values in B3:BJ16 comes from another linked spreadsheet and as soon as I moved the sheet to separate file and replaced linked values with numbers the problem was gone. Correct result can be also obtained, if original formula (like in attachment) is replaced by rounded values: =IF($A60<B$2;"";IF(SUM(B$41:B59)>=B$102;"";B$6*$B$29*1000) replaced with =IF($A60<B$2;"";IF(ROUND(SUM(B$41:B59))>=ROUND(B$102);"";B$6*$B$29*1000)). The problem appears only in few formulas. Unfortunately I cannot provide example with non-working formula, because the problem was gone after replacement of linked values with numbers.
Hi @andis, thanks for reporting. I think the issue it's that really you need to set up rounded values. Precision for calculations is limited. Please take a look to this bug report about MOD() function: https://bugs.freedesktop.org/show_bug.cgi?id=50299#c4. Specially Kohei comment #4. Please see also: https://bugs.documentfoundation.org/show_bug.cgi?id=86174 Resolved as duplicate, please if you are not agree reopen it. *** This bug has been marked as a duplicate of bug 50299 ***
Thank you for response! The problem is actually unexpected behaviour of newer version of the program. Our greenhouse gas inventory is built of libreoffice calc and it's predecessor openoffice.org since 2007 and it's the first time, when I got problems with these formulas (of course, there were other problems). It's a question of comfortable feeling using the program - should I check only my faults or should I check also results of calculations.
But if with the sample file alone, issue can't be verified, no way to find what the issue begin. Without a minimal sample files where to see the different behaviour between versions. I think very difficult if there is a way to find it. Can a be a number or formula result that now gives a periodic decimal result, producing rounding in the last number of the precision, provoking a different result on sum the products than the product of the total, that can explain why with ROUND() function works properly.
Andis' example is an excellent example of a real use of spreadsheets that most programmers don't understand and can't imagine. The SUM() bug is probably related to the same cause as bug 50299 (improper handling of significant digits in floating point calculations); however, if it is caused by something specifically in the implementation of the SUM() function, then its correction in the MOD() (and DATE()) functions will not correct it in the SUM() function. You are fortunate; you noticed that the answers were incorrect. Since I noticed the related problem with the MOD() and DATE() functions, I have returned to using Excel and am warning people that they could get unpleasant surprises if they use these functions in open source spreadsheets. As I commented on 50299, engineers, doctors and accountants cannot use spreadsheets that give random incorrect answers when used as they would normally be expected to be used. Does the problem still exist if the links in B3:BJ16 to values calculated in another document are replaced with links to values calculated in another worksheet in the example document? If so, you would be able to provide an example that shows the problem. I would not mark this as resolved, but as more information required (an example that shows the problem).
Created attachment 113401 [details] Updated version of example with repeatable error
Hello! It's funny, but when I tried to reconstruct formulas in example file using another worksheet instead of linked file, I was able to repeat the error. Result of the experiments is attached to the bug report. It is interesting, that some values are causing error ("220", "945") and some values are calculated as they should be ("375"). No problems found in version 4.3.6.2. So there is working example of the problem. Regarding use of Libreoffice in complex calculations. I would say, that it is much, much better than excel, when there is need to use lot of sumif(), lookup(), hlookup() and other array functions. Statistical functions like ttest() are not limited to equal number of values in compared ranges and so on. I can't imagine returning to Excel now; the worst case would be returning to openoffice.org, which is much better and faster with large data ranges, because CPU use is another madness introduced somewhere with 3.5.x version. Some time ago there was something wrong with calculations in pivot tables and since that I'm always checking results of calculations. But this is very uncommon that mistakes appear in basic functions. Best regards
Hi @andis have you tried with a hard recalc?. Expanding decimal places at maximum, values in: B 52: 220,2624382700770 B113: 4405,2487654015700 after a hard recalc: B 52: 220,2624382700780 B113: 4405,2487654015600 this I think now gives the same results than in 4.3.6.2 Please can you confirm.
(In reply to m.a.riosv from comment #7) > Hi @andis have you tried with a hard recalc?. > > Please can you confirm. What is hard recalc?
Forgive me, I didn't mention. Shortcut = [Shif+Ctrl+F9] It forces to recalculate all cells. https://help.libreoffice.org/Calc/Recalculate http://markmail.org/thread/vggjlicfxgpvi2xy
Just a note in support of this report. If confirmed, this bug may deserve a higher priority &/or severity. It is possible that a linked cell's current value is being taken and used without forcing an update through the link first. Or, that the value update is called for, but the calculation is not properly serialized by blocking the other parts of the calculation dependent on the pending update. Either way, results reliant on linked data would be inconsistent and unreliable. I have been testing spreadsheet formulas related to other bugs and have seen results that *may* be a symptom of serialization/dependency issues in the formula interpreter.
Hello! The problem has gone after hard recalc [Shif+Ctrl+F9]. However, after saving and reopening I got the same result as before [Shif+Ctrl+F9]. Simple recalculation of cell content [f9] has no impact on result.
Hi @Andis, I see the issue of your last comment, it happens when Autocalculate is activated.
Comment on attachment 113375 [details] Example of calculation approach What's wrong with this: =B3+B4+F3+F4+F7+F8+F9+F19, all fields are defined and have simple numbers in them. Calc gives "VALUE" error. SUM function gives same result.
Comment on attachment 113401 [details] Updated version of example with repeatable error Your problem confirms what I suspected: Calc is inconsistent on basic operations. Mine failed after I added the SUM function and then removed it.
Please Bill what is the issue in your attached files?
After many tests I've found that the problem seems related to the position of the SUM function in the expression. When I place SUM as the first term in the expression the result of the calculation is wrong. When I remove it and substitute the same terms that were operands of SUM with the appropriate plus signs, the result is correct. I can't be certain about SUM being position sensitive but it is definitely suspect.
** 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 (5.1.5 or 5.2.1 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 helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
I tried the formula on the newer version of file, where I identified the problem, with 5.1.5.2, Ubuntu 16.04, 32 bit. It looks like problem is gone or some kind of forgotten workaround is implemented.
@Bill Babb ... and others: 'SUM' is sensitive to the order of operands, '=SUM(1E16; -1E16; 1)' -> 1 , '=SUM(1; 1E16; -1E16)' -> 0, it's an effect of 'cancellation' and will truncate small operands in subtraction to different degree (between 'nearly nothing' and 'total extinction', calc and excel process ranges with different strategies, thus expect deviations,