Bug 42990 - EDITING Spreadsheet: Different handling of Text Cells in Calculations with SUM or addition of multiple cells
Summary: EDITING Spreadsheet: Different handling of Text Cells in Calculations with SU...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
(earliest affected)
3.4.4 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
Whiteboard: BSA target:6.4.0
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
Reported: 2011-11-16 06:09 UTC by webmeister
Modified: 2022-02-25 09:33 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:

ODS File with calculations with numbers as 'Text' but skipped for SUM function (12.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-11-16 06:09 UTC, webmeister

Note You need to log in before you can comment on or make changes to this bug.
Description webmeister 2011-11-16 06:09:41 UTC
Created attachment 53601 [details]
ODS File with calculations with numbers as 'Text' but skipped for SUM function

Problem description: Numbers which the user may not know are formatted as 'Text' can be used in calculations.  Yet, the SUM function skips them without any warnings or error messages.  This could lead to miscalculations without the user's knowledge.

Steps to reproduce:
1. In the attached file, you see in cell A19 a SUM of 8.  This is supposed to be a sum of the cells above and is clearly wrong.
2. If you look to the right, in column H, all the fields from column A are used in calculations without any issues, though some of them in column A are formatted as 'Text.'  This is inconsistent.
3. How do we know when the SUM function is working properly?

Current behavior: Inconsistency in dealing with numbers as text.

Expected behavior: Warnings/errors

Platform (if different from the browser): 
Browser: Mozilla/5.0 (Macintosh; U; PPC Mac OS X 10.4; fr; rv: Gecko/2011091218 Camino/2.0.9 (MultiLang) (like Firefox/3.0.19)
Comment 1 Jean-Baptiste Faure 2011-11-16 21:47:17 UTC
Reproducible with LibreOffice 3.4.4 under Ubuntu 10.04 x86_64 but not exactly as described. In my case, column H shows several error codes #VALUE!. It is for values in column A less than 1 if I interpret these values as decimal numbers (nothing before the dot). For rows 5 and 10 the values seems to be interpreted as dates and column H make the product of a date by something else. I don't see what that can be meaning.

The case of SUM is clear and consistent for me: it ignores all text values. You can easily identify text which looks like numbers if you check the option "Value highlighting" in Tools > Options > LibreOffice Calc > View.
More, if you clear direct formatting in column A, you can see that the "numbers" with decimal dot are shifted to the left side of the cell. That means they are interpreted by Calc as text not as numbers. 

Best regards. JBF
Comment 2 Rainer Bielefeld Retired 2011-12-01 05:39:14 UTC
Most people would really dislike such error messages. An example: In a quotation, bill or something similar you might have several values in a column that will have to be be added for the total sum. But some fields in the column might contain comments ("will be delivered later", "facultative"), and I would not be happy if LibO would create error messages instead of calculating a sum.

So for me (ad I believe for many other users) this is NOT ab bug, but intended and desired behavior.

I believe we need a more general approach.

In a first step inconsistencies will have to be gained and analyzed. 
The result should be a list with Functions, expected behavior for several cases (no value, test, ...) with reasons for what "real life applications" that behavior is favourable,, actual behavior, comparison with Escel, Gnumeric and may be others. That could be a base for improving consistence, if required. 

We had some discussion concerning similar issues here <http://listarchives.documentfoundation.org/www/discuss/msg06532.html>, and (for example) Regina Henschel has a lot of knowledge concerning these issues.

The question is who will start the work.

Can you contribute a brief assessment whether such effort is promising - or not required because there is no known inconsistence?
Comment 3 Regina Henschel 2011-12-01 09:17:22 UTC
There is no choice about the behavior, but we have to follow the definition in the ODF1.2 standard. http://docs.oasis-open.org/office/v1.2/
In 6.16.61 SUM notice "(see the text on conversions)". The conversion is described in 6.3.7 Conversion to NumberSequence.

So it is intended behavior that SUM and + behave different on texts.
Comment 4 webmeister 2011-12-01 11:44:43 UTC
Regina summed it up well.  It is inconsistent that in the SUM function they are skipped, but in other calculations, they are not.
Comment 5 Jean-Baptiste Faure 2011-12-01 13:03:44 UTC
Thank you very Regina for your comment. For me this means we need at least to
improve the help of SUM function
(http://help.libreoffice.org/Calc/Mathematical_Functions#SUM) but I am not sure
it will be sufficient to make the information reaching the end-users. Is there
a comprehensive text about conversion text <-> number already available in the
Calc documentation? If there is one, we can duplicate and translate it in our
FAQ. If there is not, who has the skill and the talent to write one? ;-)

After some search in online help of Calc, I finally found that:
http://help.libreoffice.org/Calc/Converting_Text_to_Numbers. Do you think it is

Best regards. JBF
Comment 6 Regina Henschel 2011-12-01 13:40:56 UTC
http://help.libreoffice.org/Calc/Converting_Text_to_Numbers is not sufficient. It misses the different handling of single numbers and number sequences from referenced cell ranges.

http://help.libreoffice.org/Calc/Mathematical_Functions#SUM is not sufficient. It lacks the text "SUM ignores any text or empty cell within a range or array"
as seen in
Comment 7 Rainer Bielefeld Retired 2011-12-01 22:15:48 UTC
For me the summary seems to be: all behavior is intended, but Help and documentation still are incomplete and not suitable for end user?

Additionalla I believe the wording on <http://help.libreoffice.org/Calc/Converting_Text_to_Numbers> is imprecise. Calc does NOT CONVERT strings looking like calendar and time information to numbers, but it TREATS such strings as if they were numbers in calculations.

Any objections to 
- Change Component to "Documentation"
- involve Kohei and David (+ documentation team) to improve Help and manual?
Comment 8 Jean-Baptiste Faure 2011-12-04 11:49:36 UTC
(In reply to comment #7)
> [...]
> Any objections to 
> - Change Component to "Documentation"
> - involve Kohei and David (+ documentation team) to improve Help and manual?

I agree.

Best regards. JBF
Comment 9 Rainer Bielefeld Retired 2011-12-10 09:22:08 UTC
@Kohei, David
Can you please try to get Help and Documentation more instructive concerning intentions of the observed behavior?
Comment 10 sophie 2013-09-15 10:02:16 UTC
Assigned to me - David, if you want it back, just take over - Sophie
Comment 11 Thomas Lendo 2017-06-05 20:30:38 UTC
@Sophie: Do you still want to keep the report assigned to you? There is no action since 2013.
Comment 12 Xisco Faulí 2017-09-11 08:27:10 UTC
Dear developer,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.
Comment 13 Emma 2019-09-06 06:21:02 UTC Comment hidden (spam)
Comment 14 Commit Notification 2019-09-06 16:14:32 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":


tdf#42990 Different handling of Text Cells in Calc
Comment 15 archana 2019-12-30 09:40:29 UTC Comment hidden (spam)
Comment 16 Sophia 2021-04-13 13:07:17 UTC Comment hidden (spam)
Comment 17 Katharine J Walker 2022-01-25 04:43:50 UTC Comment hidden (spam)
Comment 18 Melina w walker 2022-02-25 09:13:20 UTC Comment hidden (spam)