Bug 75834 - Calc inconsistently sums values if numbers and text are mixed
Summary: Calc inconsistently sums values if numbers and text are mixed
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:4.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2014-03-06 11:57 UTC by a07cd040897db54e103c
Modified: 2014-03-17 11:27 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
An example document (17.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-03-06 11:57 UTC, a07cd040897db54e103c
Details

Note You need to log in before you can comment on or make changes to this bug.
Description a07cd040897db54e103c 2014-03-06 11:57:22 UTC
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).
Comment 1 Eike Rathke 2014-03-06 12:47:14 UTC
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
Comment 2 Eike Rathke 2014-03-14 12:39:58 UTC
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.
Comment 3 a07cd040897db54e103c 2014-03-17 09:04:18 UTC
(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...
Comment 4 Eike Rathke 2014-03-17 11:27:08 UTC
(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.