Created attachment 95225 [details] An example document If you have numeric and string values (both representing numbers) mixed, the SUM()-function ignores the string values and only adds up the numeric values. This alone does not seem to be a bug because one could argue, that adding text values to numeric values is nonsense. On the other hand manual, adding up the cells (=A1+A2+A3....) *includes* the string values. As a result, SUM() and A1+A2+A3... produce different results. I think both methods should produce the same result (either *both* ignoring strings, or *both* including string).
There's a 3rd variant, ignore strings on a number series (as a range in SUM is) because this is what all spreadsheets do; and generate a #VALUE! error for strings that can not be interpreted as a number (or date for that matter) without locale dependent information (separators and so on) and calculate them for the simple cases, because this is what some spreadsheets do. Luckily the code is already prepared for that, we need some means to de/activate the feature on user will to not force everyone to rewrite documents. I'll see if I can take care of this for 4.3
Implemented with http://cgit.freedesktop.org/libreoffice/core/commit/?id=c52f3ea0eb327343b1945290c43d3b66f546dfe9 Note that even with "Treat as zero" you may still get different results, for example =AVERAGE(A1:A2) is different from =(A1+A2)/2 because the number sequence generated from a range reference ignores text cells instead of converting them to 0.
(In reply to comment #2) > Implemented with > http://cgit.freedesktop.org/libreoffice/core/commit/ > ?id=c52f3ea0eb327343b1945290c43d3b66f546dfe9 Didn't check the sources, but expect that this only fixes the problem with "=SUM(A1:A2)" vs. "=A1+A2". I guess the problem is more general: different handling of cells in ranges and single cells. What about ="PRODUCT(A1:A2)" vs. "=A1*A2"? Will all functions that accept ranges now calculate the same reasult as you would get when you manually work with single cells? > Note that even with "Treat as zero" you may still get different results, for > example =AVERAGE(A1:A2) is different from =(A1+A2)/2 because the number > sequence generated from a range reference ignores text cells instead of > converting them to 0. I guess, that will produce similar irritations in the future. I feel the underlying bug might not really be fixed. Why can't your "Treat-as-zero" strategy (or any other string-handling-strategy) be applied to both? Then IMO they would both calculate the same results...
(In reply to comment #3) > I guess the problem is more general: different handling of cells in ranges > and single cells. > > What about ="PRODUCT(A1:A2)" vs. "=A1*A2"? > Will all functions that accept ranges now calculate the same reasult as you > would get when you manually work with single cells? No. Please understand that a function that accepts a number sequence of a cell range ignores all text and empty cells. This is how all spreadsheet applications behave. This will not be changed.
*** Bug 116332 has been marked as a duplicate of this bug. ***