If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
In cell A7 I get the correct sum if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
If I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )
Steps to Reproduce:
1. Open a new Calc spreadsheet, and paste in the following numbers in a column ( cells A1 to A6):
2) Select column, and press the sum icon; A7 formula is now =SOMMA(A1:A6) (italian version) and the total is wrong --> 0,0016177
3) In cell A7 write the formula = A1 +A2 + A3 + A4 + A5+ A6; correct answer is given
SOMMA(A1:A6) = 0,0016177
SOMMA(A1:A6) = 0,01659882
User Profile Reset: No
Build ID: Gentoo official package
Thread CPU: 8; SO: Linux 4.15; Resa interfaccia: predefinito; VCL: gtk3;
Versione locale: it-IT (it_IT.UTF-8); Calc: group
Same problem was present with the previous version of Libreoffice
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36
No reproduce with
CPU 线程：4; 操作系统：Linux 4.14; UI 渲染：默认; VCL: gtk2;
区域语言：zh-CN (zh_CN.UTF-8); Calc: group
Build ID: 6b8ed514a9f8b44d37a1b96673cbbdd077e24059
CPU Threads: 4; OS Version: Linux 4.14; UI Render: default; VCL: gtk2; Layout Engine: new;
Locale: zh-CN (zh_CN.UTF-8); Calc: group
Fedora 27 x64.
Could you give a screenshot of the cells shown.
0,0016177 is apparently the value of the A3. Are all other cells formatted (and shown) as numbers or text?
Created attachment 139819 [details]
Screenshot number one
All cells are formatted as numbers
Created attachment 139820 [details]
screenshot adding cells one by one
Created attachment 139822 [details]
Example bug spreadsheet
What's the result if you reset your libreoffice user profile (~/.config/librefiice). Yoy can rename it so you do not lose your data.
I did that, the problem persists.
Definitely only 0,0016177 is interpreted as number for me in that spreadsheet. Recalculating A7 gives me #VALUE! as result.
So it seems that the bug is that on my version some cells are occupied by invalid numbers but these seem to be valid?
What does B7 give you?
I though the problem might have been that the original column of numbers was copy-pasted, so I tried inserting the numbers one by one by hand in a new column.
I get 0,0016177 in B7. First I tested with US locale, and it was obvious from "," being used instead of the normal "." decimal pointer which values were interpreted as numbers.
If you select all the numbers and clear direct formatting, the ' that denotes numeric data that isn't interpreted as numbers, appears (and can be removed, and finally numbers are numbers again).
Ok, I did as you suggested, and it works.
In my original spreadsheet (which is larger and more complicated) I even found some zeroes which I definitely added by hand and not via "copy and paste" which were not numbers. So there is a question about what happened to transform some numbers into ascii characters.
So, the bug is that the sum of invalidly formatted numbers should give me #VALUE! just as it does for you, and not a wrong answer if I use SUM(A1:A6) and the right answer if I sum cell by cell.
A localization bug?
> Invalidly formatted numbers fail to trigger #VALUE! in formula
But how to reproduce this? Do you have steps?
For me, my locale decimal separator is ".", so:
A4: =SUM(A1:A3) --> 1.1111
A5: =A1+A2+A3 --> #VALUE!
As you can see, A1 and A2 are invalid numbers (for my locale). So:
1. SUM(A1:A3) results 1.1111, this is correct. I think MS Excel will get the same result.
2. A1+A2+A3 results #VALUE!. This may also be correct and is expected.
The above will be the same if you type
and then you will get:
SUM(A1:A3) = 1
A1+A2+A3 = #VALUE!
CPU 线程：2; 操作系统：Linux 4.14; UI 渲染：默认; VCL: gtk2;
区域语言：zh-CN (zh_CN.UTF-8); Calc: group
In the example spreadsheet I uploaded, the formula in A7 is = A1+A2+A3+A4+A5+A6 and in Aron Budea's spreadsheet this gives #VALUE! whereas in mine it gives the correct result (as you can see from screenshots)
The formula in B7 is =SOMMA(B1:B6) and it only sums the one value which is correctly formatted as a number, disregading the others.
So, for some reason, (localization?) in my version of Calc the wrongly formatted numbers are summed in the A7 formula but correctly disregarded in the B7 formula.
In my case the wrongly formatted numbers can ony be seen by removing direct formatting, and in this case the ' appears before the number like this '0,00456
I have no idea how such numbers were actually inserted to start with (maybe copying a number from text and pasting it?)
I think you can reproduce this as follows:
1) create a column of numbers
2) remove direct formatting, then manually add the ' character before some of them, and save
3) at this point the file you have is like mine. On my version of Calc summing the cells should still work whereas on yours it should give #VALUE!
This is not a bug. For SUM and other functions that take a cell range argument that is defined to be a NumberSequence, cells with content of type Text by definition are ignored. They are not to produce an error value. This is the same in every major spreadsheet implementation.
In the case of the attached sample document, only cells A3 and B3 have a content of type Number (0.0016177), the other cells have content of type Text so are ignored.
Note that this is not about "invalidly formatted numbers" like the title says, instead it is about the cell content *type*.
Also note that the behaviour of calculations with operator + and text strings that look like numbers may depend on the current locale and its separators and the settings under Tools -> Options -> Calc -> Formula, Detailed Calculation Settings, conversion of text to numbers. My personal recommendation is to set that under Custom to "Generate #VALUE! error" to spot data type problems early, and specifically not use the "Convert also locale dependent" setting.
The leading apostrophe in '0,00456 displayed in the input line indicates that the cell content is of type Text but could be interpreted as number (in the current locale). Actually it can be converted to numeric content, for details see https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data