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:1.9.0.19) Gecko/2011091218 Camino/2.0.9 (MultiLang) (like Firefox/3.0.19)
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
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. @Regine: Can you contribute a brief assessment whether such effort is promising - or not required because there is no known inconsistence?
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.
Regina summed it up well. It is inconsistent that in the SUM function they are skipped, but in other calculations, they are not.
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 sufficient? Best regards. JBF
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 http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_SUM_function
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?
(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
@Kohei, David Can you please try to get Help and Documentation more instructive concerning intentions of the observed behavior?
Assigned to me - David, if you want it back, just take over - Sophie
@Sophie: Do you still want to keep the report assigned to you? There is no action since 2013.
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.
You can modify Text Cells in the Spreadsheet according to your needs and the way you want to use it for your project. The https://www.bestessaytips.com/phd.php share a vide guide in which they explain all the features of the spreadsheet.
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/+/b2a9c3b29c29c417574890b27783da777702cabf%5E%21 tdf#42990 Different handling of Text Cells in Calc
Such an impressive share! It is well-written and contains all the info, your Post is very unique and all information is reliable for new readers.I`m working as an academic consultant in USA and offers writing help to college students. https://sites.google.com/site/bestessaywritingservicereview/
Most individuals would truly abhorrence such mistake messages. A model: In a citation, bill or something comparative you may have a few qualities in a section that should be added for the all out entirety. Be that as it may, a few fields in the segment may contain remarks ("will be conveyed later", "facultative"), and I would not be glad if LibO would make blunder messages as opposed to computing a whole. https://essaysnassignments.co.uk/cheap-essay/
Nice post. https://sites.google.com/site/bestessaywritingservicereview/
For any academic writing services, follow https://sites.google.com/site/bestessaywritingservicereview/