Apache OpenOffice (AOO) Bugzilla – Issue 125778
Text in cells -> zero
Last modified: 2015-04-24 16:02:23 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.
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
I change the component to open-import because spreadsheets saved with versions before AOO 3.4 may show wrong results with AOO >= 3.4.
>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
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)
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.
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.
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.
Yes. That's why text better should converted to zero, at least optionally, as mentioned in Post #1.
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.