Issue 125778 - Text in cells -> zero
Text in cells -> zero
 Status: CONFIRMED None Calc Application open-import (show other issues) 3.4.0 All All P3 Normal (vote) --- AOO issues mailing list regression

 Reported: 2014-10-21 11:17 UTC by David4 2015-04-24 16:02 UTC (History) 3 users (show) mroe.nospam oliver.brinzing villeroy DEFECT 4.2.0-dev ---

Attachments
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.
 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.``` 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``` 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.` 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. https://issues.apache.org/ooo/show_bug.cgi?id=5658#c214``` 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)` 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.` 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.``` 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.``` 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.` 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.```