Bug 115642 - Invalidly formatted numbers fail to trigger #VALUE! in formula; was: SUM function in Calc gives wrong answer
Summary: Invalidly formatted numbers fail to trigger #VALUE! in formula; was: SUM fun...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
5.3 all versions
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2018-02-12 08:35 UTC by michele
Modified: 2018-03-01 14:13 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Screenshot number one (89.20 KB, image/png)
2018-02-12 09:30 UTC, michele
screenshot adding cells one by one (89.93 KB, image/png)
2018-02-12 09:32 UTC, michele
Example bug spreadsheet (11.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-12 09:43 UTC, michele

Note You need to log in before you can comment on or make changes to this bug.
Description michele 2018-02-12 08:35:21 UTC
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 

Actual Results:  
SOMMA(A1:A6) = 0,0016177

Expected Results:
SOMMA(A1:A6) = 0,01659882

Reproducible: Always

User Profile Reset: No

Additional Info:
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

Installed versions: 10/02/2018)(bluetooth branding cups dbus gtk java kde pdfimport -coinmp -debug -eds -firebird -googledrive -gstreamer -gtk2 -jemalloc -libressl -mysql -odk -postgres -test -vlc ELIBC="-FreeBSD" LIBREOFFICE_EXTENSIONS="nlpsolver -scripting-beanshell -scripting-javascript -wiki-publisher" PYTHON_SINGLE_TARGET="python3_6 -python2_7 -python3_4 -python3_5" PYTHON_TARGETS="python2_7 python3_6 -python3_4 -python3_5")

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
Comment 1 Kevin Suo 2018-02-12 08:47:46 UTC
No reproduce with

Build ID:60bfb1526849283ce2491346ed2aa51c465abfe6
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.
Comment 2 Kevin Suo 2018-02-12 09:14:45 UTC
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?
Comment 3 michele 2018-02-12 09:30:58 UTC
Created attachment 139819 [details]
Screenshot number one

All cells are formatted as numbers
Comment 4 michele 2018-02-12 09:32:36 UTC
Created attachment 139820 [details]
screenshot adding cells one by one
Comment 5 michele 2018-02-12 09:43:06 UTC
Created attachment 139822 [details]
Example bug spreadsheet
Comment 6 Kevin Suo 2018-02-12 09:48:07 UTC
What's the result if you reset your libreoffice user profile (~/.config/librefiice). Yoy can rename it so you do not lose your data.
Comment 7 michele 2018-02-12 09:51:58 UTC
I did that, the problem persists.
Comment 8 Aron Budea 2018-02-12 09:59:54 UTC
Definitely only 0,0016177 is interpreted as number for me in that spreadsheet. Recalculating A7 gives me #VALUE! as result.
Comment 9 michele 2018-02-12 10:07:19 UTC
So it seems that the bug is that on my version some cells are occupied by invalid numbers but these seem to be valid?
Comment 10 michele 2018-02-12 10:08:10 UTC
What does B7 give you?
Comment 11 michele 2018-02-12 10:40:18 UTC
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.
Same problem
Comment 12 Aron Budea 2018-02-12 12:56:49 UTC
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).
Comment 13 michele 2018-02-12 13:14:25 UTC
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?
Comment 14 Kevin Suo 2018-02-12 13:27:07 UTC
> 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:
A1: 1,2345
A2: 2,4567
A3: 1.1111
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!

Build ID:60bfb1526849283ce2491346ed2aa51c465abfe6
CPU 线程:2; 操作系统:Linux 4.14; UI 渲染:默认; VCL: gtk2; 
区域语言:zh-CN (zh_CN.UTF-8); Calc: group
Comment 15 michele 2018-02-12 14:06:24 UTC
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!
Comment 16 Eike Rathke 2018-02-12 15:01:05 UTC
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