Bug 115642 - Invalidly formatted numbers fail to trigger #VALUE! in formula; was: SUM function in Calc gives wrong answer
Invalidly formatted numbers fail to trigger #VALUE! in formula; was: SUM fun...
 Status: CLOSED NOTABUG None LibreOffice Unclassified Calc (show other bugs) 5.3 all versions x86-64 (AMD64) Linux (All) medium normal Not Assigned

 Reported: 2018-02-12 08:35 UTC by michele 2018-03-01 14:13 UTC (History) 2 users (show) aron.budea suokunlong

Attachments
Screenshot number one (89.20 KB, image/png)
2018-02-12 09:30 UTC, michele
Details
screenshot adding cells one by one (89.93 KB, image/png)
2018-02-12 09:32 UTC, michele
Details
 michele 2018-02-12 08:35:21 UTC ```Description: If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6): 0,0086584 0,00159885 0,0016177 0,00164682 0,00151831 0,00155874 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): 0,0086584 0,00159885 0,0016177 0,00164682 0,00151831 0,00155874 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: Versione: 6.0.1.1 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 app-office/libreoffice Installed versions: 6.0.1.1(15:14:00 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``` Kevin Suo 2018-02-12 08:47:46 UTC ```No reproduce with Version: 6.0.1.1 Build ID：60bfb1526849283ce2491346ed2aa51c465abfe6 CPU 线程：4; 操作系统：Linux 4.14; UI 渲染：默认; VCL: gtk2; 区域语言：zh-CN (zh_CN.UTF-8); Calc: group Version: 5.3.7.2 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.``` 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?``` michele 2018-02-12 09:30:58 UTC ```Created attachment 139819 [details] Screenshot number one All cells are formatted as numbers``` michele 2018-02-12 09:32:36 UTC ```Created attachment 139820 [details] screenshot adding cells one by one``` michele 2018-02-12 09:43:06 UTC ```Created attachment 139822 [details] Example bug spreadsheet``` 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.` michele 2018-02-12 09:51:58 UTC `I did that, the problem persists.` 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.` 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?` michele 2018-02-12 10:08:10 UTC `What does B7 give you?` 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``` 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).``` 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?``` 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 a b 1 and then you will get: SUM(A1:A3) = 1 and A1+A2+A3 = #VALUE! Version: 6.0.1.1 Build ID：60bfb1526849283ce2491346ed2aa51c465abfe6 CPU 线程：2; 操作系统：Linux 4.14; UI 渲染：默认; VCL: gtk2; 区域语言：zh-CN (zh_CN.UTF-8); Calc: group``` 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!``` 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```