Created attachment 103709 [details] Spreadsheet illustrating the bug. A CSV file When summing column J of the attached spreadsheet the result is 0. Adding each cell individually works fine.
Created attachment 103710 [details] An ODS illustrating the bug
Hello Worik, Thank you for filing the bug. Using the attached CSV, i used SUM() and it did calculate it fine for me on 4.2.5 on Linux Mint. With the ODS, the column A has numbers in the cells but the format of the cells are 'text' and as such, SUM() doesnt work on those cells. I opened the same ODS in excel 2010 and it also showed '0'. How would you like to proceed with this bug report.
On 31/07/14 15:51, bugzilla-daemon@freedesktop.org wrote: > https://bugs.freedesktop.org/show_bug.cgi?id=81935 > > Jay Philips <philipz85@hotmail.com> changed: > > What |Removed |Added > ---------------------------------------------------------------------------- > Status|UNCONFIRMED |NEEDINFO > CC| |philipz85@hotmail.com > Ever confirmed|0 |1 > > --- Comment #2 from Jay Philips <philipz85@hotmail.com> --- > Hello Worik, > > Thank you for filing the bug. Using the attached CSV, i used SUM() and it did > calculate it fine for me on 4.2.5 on Linux Mint. With the ODS, the column A has > numbers in the cells but the format of the cells are 'text' and as such, SUM() > doesnt work on those cells. I opened the same ODS in excel 2010 and it also > showed '0'. How would you like to proceed with this bug report. The bug is a difference between addition (=C1+C2+C3) Vs. SUM (=SUM(C1:C3). This is a bug, SUM is the same as repeated addition, unless I am missing something obvious! cheers Worik >
On 31/07/14 15:51, bugzilla-daemon@freedesktop.org wrote: > https://bugs.freedesktop.org/show_bug.cgi?id=81935 > > Jay Philips <philipz85@hotmail.com> changed: > > What |Removed |Added > ---------------------------------------------------------------------------- > Status|UNCONFIRMED |NEEDINFO > CC| |philipz85@hotmail.com > Ever confirmed|0 |1 > > --- Comment #2 from Jay Philips <philipz85@hotmail.com> --- > Hello Worik, > > Thank you for filing the bug. Using the attached CSV, i used SUM() and it did > calculate it fine for me on 4.2.5 on Linux Mint. With the ODS, the column A has > numbers in the cells but the format of the cells are 'text' and as such, SUM() > doesnt work on those cells. I opened the same ODS in excel 2010 and it also > showed '0'. How would you like to proceed with this bug report. > And another thing: When this problem is encountered there is no way (I can find) of changing a column of strings into a column of numbers. Worik
Hi Worik, If it only happened in Calc and didnt in Excel, then it is possible that it could be classified as a bug IMHO, but i will let someone more experience than I at Calc have a look at the issue. Regarding the converting of cells from strings to numbers, I also wasnt able to convert them, while Excel provided a simple means of doing so.
Same error in XP Versión: 4.2.5.2 Id.: 6ff819b65674ae6c83f3cbab9e4a4c2b292a7a94 The numbers have a tick " ' "at the beginning to force them as text (converted from csv?) The problem is that there is no easy way of turning them into numbers. The only way is going cell by cell and remove the tick. You can't run a replace command (you get "not found"). You can't change the format. If you input "25%" you can change the format from percentage to standard number without removing then "%" from every cell. Why text with the " ' " is different? Suggestion: When you set the format of a cell to number, it should remove the starting tick when there is one.
On 31/07/14 20:08, bugzilla-daemon@freedesktop.org wrote: > https://bugs.freedesktop.org/show_bug.cgi?id=81935 > > --- Comment #6 from svampa <svaa@ciberpiula.net> --- > Same error in XP > Versión: 4.2.5.2 > Id.: 6ff819b65674ae6c83f3cbab9e4a4c2b292a7a94 > > The numbers have a tick " ' "at the beginning to force them as text (converted > from csv?) > The problem is that there is no easy way of turning them into numbers. The only > way is going cell by cell and remove the tick. You can't run a replace command > (you get "not found"). You can't change the format. > > If you input "25%" you can change the format from percentage to standard number > without removing then "%" from every cell. Why text with the " ' " is > different? > > Suggestion: When you set the format of a cell to number, it should remove the > starting tick when there is one. > These numbers (if you look at the atachment ) have double quotes both ends Worik
During import, If, under "Other Options", you remove the checkmark from "Quoted field as text", the data in column J imports as a number instead of text. The use of single quote "ticks" to force numbers to text is a common practice (think zip code entry to prevent loss of leading zeros). If the conversion must be done after the import, it is common to add another column (say between J and K) and then insert the function =value(J2) into the new column, duplicating as needed. This article might have some bearing. There's a format code of the at symbol @ which is invoked in the cells of column C. https://help.libreoffice.org/Common/Number_Format_Codes I did see the odd result of =sum(c2:c7) being equal to zero while =c2+c3+c4+c5+c6+c7 gave a value of 79647. The inconsistency is odd.
Created attachment 103753 [details] Test file illustrating sum vs cell-by-cell addition Test System: Kubuntu 14.4 (KDE 4.13.2) LibreOffice 4.2.4.2 There is a difference in behavior between the way =sum() and cell-by-cell addition work. Sum is designed to ignore text/label cells, adding only the cells which contain number data. Please take a look at the attachment for a more detailed look. This may not be a bug, but an example the spreadsheet rules being different for the two methods.
Whiteboard: Remove 'needQAAdvice' once bug is NEW/RESOLVED.