Description: When using SUM() function, it does not calculate values Steps to Reproduce: 1. pastes values into a column 2. enter =SUM(A1:A3) or use the sum function shortcut and select range Actual Results: It always displays zer, even if you change the cell format to "number", or enter any numeric value Expected Results: It should calculate the sum value of the numbers in the selected cell range Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
Created attachment 175970 [details] Document where SUM function does not work
in Column G and E, the numbers start with a ', that's why the SUM doesn't work. Just remove it. Closing as RESOLVED NOTABUG
1. I do not see the quotes, but thanks, will look at it again 2. Here is something I can maybe add: - When you type 1 and then space, it will convert it to "1,00" and that works, but when you type "123.45" 9having a decimal in the number, it will not work - When I use "123,45" (it also does does not work - Changing the cell format between text and number format has no effect. When it is float numbers, it does not add up Please play around with it on the attached document and using the version specified. you will see what I mean. I spend quite a bit of time, and i use the function quite a lot, so I am sure I am not putting in junk quotes in the numeric text. I am entering them via the keyboard by pressing the numeric key. the input should be clean that way. yet when it comes to certain number (perhaps decimal types) it fails to calculate.
I don't have 7.0.6.2 installed, but when I open the file in 7.3dev, I also see the single quote for each entry in columns E and G as noted in Comment 3.
(In reply to Michael Warner from comment #5) > I don't have 7.0.6.2 installed, but when I open the file in 7.3dev, I also > see the single quote for each entry in columns E and G as noted in Comment 3. That is interesting. The quotes are not visible on my side, not in the cell, nor on the cell editor box on the top. and even if the cells are cleared, it must then be that hidden characters are not displayed. Let me upload a screenshot.
Created attachment 175974 [details] Screenshot to show dfference between SUM functions Observation on entering different values where SUM function does not work
PS. I also tried changing the decimal separator key, but thus far it did not have an effect on the outcome.
If you use View > Value Highlighting, then text is black, numbers are blue and formulas are green. In that mode you see, that cell content in E3:E10 and G3:G10 is text. Changes in cell format on existing content cannot be used for changes between data type text and data type number. To change existing 'text with decimal point' to 'number with comma separator', you can do these steps: 1. cut the text to clipboard 2. Format the (still marked) cells to "General". That is in Format > Cells > Tab numbers. Make sure the field 'language' in that tab is correct. 3. (cells still marked) Use the item "Unformated text (TSV-Calc)" from the drop-down list of the 'Paste' button in the toolbar. You get the "Text Import" dialog. 4. In that dialog click on column header (shows "Standard" as default) in the section 'Fields' and then select item "US English" in field 'Column type'. OK. To change the display of the formula result in cell E11 to "number with comma separator", go to Format > Cell > Tab numbers. Change the language to -in your case- "English (South Africa)". Do all your tries on a copy of the document! For more assistance in formatting and data type handling in Calc please use user mailing list or Ask. For me this is not a bug.
(In reply to Regina Henschel from comment #9) > If you use View > Value Highlighting, then text is black, numbers are blue > and formulas are green. In that mode you see, that cell content in E3:E10 > and G3:G10 is text. > > Changes in cell format on existing content cannot be used for changes > between data type text and data type number. > > To change existing 'text with decimal point' to 'number with comma > separator', you can do these steps: > 1. cut the text to clipboard > 2. Format the (still marked) cells to "General". That is in Format > Cells > > Tab numbers. Make sure the field 'language' in that tab is correct. > 3. (cells still marked) Use the item "Unformated text (TSV-Calc)" from the > drop-down list of the 'Paste' button in the toolbar. You get the "Text > Import" dialog. > 4. In that dialog click on column header (shows "Standard" as default) in > the section 'Fields' and then select item "US English" in field 'Column > type'. OK. > > To change the display of the formula result in cell E11 to "number with > comma separator", go to Format > Cell > Tab numbers. Change the language to > -in your case- "English (South Africa)". > > Do all your tries on a copy of the document! > > For more assistance in formatting and data type handling in Calc please use > user mailing list or Ask. > > For me this is not a bug. thanks. I basically simply do a mass replace of period to comma. I am unable to find the place where it is set as a dot. It did not used to exhibit this behaviour so I am not sure where this chnaged in the locale settings. Will close because it basically is a formatting issue where commas must be used, and not periods as decimal separator.
https://bugs.documentfoundation.org/page.cgi?id=fields.html#bug_status