Issue 125778 - Text in cells -> zero
Summary: Text in cells -> zero
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 3.4.0
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: regression
Depends on:
Reported: 2014-10-21 11:17 UTC by David4
Modified: 2015-04-24 16:02 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.2.0-dev
Developer Difficulty: ---

difference between addition and SUM()-function and between multiplication and PRODUCT()-function (9.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-21 14:04 UTC, mroe
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description David4 2014-10-21 11:17:58 UTC
Problem: When adding cells (or doing other calculation) which contain text, a error occurs. 
In older versions of OO, text was handled as zero. This should be re-enabled again, possibly as an option. Excel and LibreOffice can calculate with such cells too. (LO has a setting for that: Tools → Options → LibreOffice Calc → Formula in Detailed calculation settings → Custom → Details from Conversion from text to number)
Other issues while opening *.sxc can be corrected more easy, but thousands of formulas can't... Thanks.
Comment 1 mroe 2014-10-21 14:04:52 UTC
Created attachment 84095 [details]
difference between addition and SUM()-function and between multiplication and PRODUCT()-function

In my opinion =A1+A2 should provide the same result as =SUM(A1;A2) or =SUM(A1:A2) regardless of the content of the cells A1 and A2.

Cells with text content should be ignored in all calculations; treated as 0 for addition and as 1 for multiplication.

BTW: In versions _before_ AOO text values in a multiplication was treated as 0 but as 1 in PRODUCT(); for addition and SUM() always as 0.
With a number in A1 and text in A2:
=A1*A2 result 0
=PRODUCT(A1;A2) result A1
=A1+A2 result A1
=SUM(A1;A2) result A1
Comment 2 mroe 2014-10-21 14:08:14 UTC
I change the component to open-import because spreadsheets saved with versions before AOO 3.4 may show wrong results with AOO >= 3.4.
Comment 3 Oliver Brinzing 2014-10-21 17:24:25 UTC
>In my opinion =A1+A2 should provide the same result as =SUM(A1;A2) 
>or =SUM(A1:A2) regardless of the content of 

This behaviour has been changed some years ago:

Converts string content to numeric value, or sets #VALUE! error if no
unambiguous conversion is possible.
Comment 4 David4 2014-10-25 12:12:14 UTC
mroe, sure that this is the optimal "component"? I choosed "configuration" because apparently some users prefer getting the error message and the best solution would be to make this behaviour configurable. Possibly its better to change it back (if there's a chance that it will be noticed by developers)
Comment 5 mroe 2014-11-07 15:37:55 UTC
In this case I think configuration is not a good solution. A user get a document, open it and want to see the right calculation. In my opinion the result must not depend on any configuration.
Comment 6 David4 2015-04-24 13:13:11 UTC
No news to this bug? 
A fix is strongly needed, otherwise switching to LibreOffice can not avoided. Until now we keep 2 OO-versions side by side.
Comment 7 Andreas Säger 2015-04-24 13:45:29 UTC
OpenOffice does it right.
LibreOffice and Excel do it wrong.

In LibreOffice (English locale) I enter this in A1, A2, A3:
A1: '3.456
A2: '1.234
A3: =A1+B1 ==> 4.69

Let's simulate what happens when I send the file abroad.
-- Switch Tools>Options>LanguageSettings>Languages>Locale from English to German (or French or Russian or Italian or ...).
-- Reload the file or force recalculation by Ctrl+Shift+F9
Now the exact same formula using the exact same text values yields a completely different result 4690.
Comment 8 David4 2015-04-24 15:15:43 UTC
Yes. That's why text better should converted to zero, at least optionally, as mentioned in Post #1.
Comment 9 mroe 2015-04-24 16:02:23 UTC
Please don't change the version field to a newer version!
The field should always contain the earliest version where the issue occurs. This helps the developers to find the root of the problem.

Latest Confirmation shows the version until that the issue occurs.