Description: The function SUM() does not produce the same result as the '+' operator in certain situations. I have the feeling this should not be. Steps to Reproduce: 1. enter "'1" in cell A1 (without the ") 2. enter "'2" in cell B1 (without the ") 3. enter "=sum(a1:b1)" in cell C1 (without the ") 4. enter "=a1+b1" in cell D1 (without the ") 5. watch Actual Results: the two cells C1 and D1 do not contain the same value Expected Results: The two cells C1 and D1 should contain the same value (either 0 or 3, I have no definitive answer on that point). I assume that when using the '+' operator, an automatic conversion to numerical values is done. If so, then why isn't that conversion also done when using SUM() ? This is odd. Reproducible: Always User Profile Reset: No Additional Info: The numerical values in A1 and B1 are in fact imported from another tool that (who knows why) exports them as strings, thus they are prefixed with "'" If you feel this is not a bug but a "feature", could you give me a link on where that behavior is documented? Thank you. Version: 6.0.6.2 Build ID: 1:6.0.6-0ubuntu0.14.04.1 CPU threads: 8; OS: Linux 4.4; UI render: default; VCL: gtk2; Locale: fr-FR (en_US.UTF-8); Calc: group
Cell with SUM contains value 0, cell with operator + contains value 3
No repro with Version: 7.0.0.0.alpha1 Build ID: 6a03b2a54143a9bc0c6d4c7f1... CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; VCL: osx; Locale: nl-NL (nl_NL.UTF-8); UI: en-US Calc: threaded
Created attachment 161513 [details] document with the problem for easy test Confirm it with Version: 6.4.4.2 (x64) Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: GL; VCL: win; Locale: ro-RO (ro_RO); UI-Language: en-US Calc: threaded
Both should be zero, or both should be 3.
(In reply to Telesto from comment #2) > No repro with > Version: 7.0.0.0.alpha1 > Build ID: 6a03b2a54143a9bc0c6d4c7f1... > CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; VCL: osx; > Locale: nl-NL (nl_NL.UTF-8); UI: en-US > Calc: threaded Ok, so this could actually be fixed in latest release. Maybe someone else can check? I'd prefer not to because I really need a stable version, I'll wait a little until release 7 is ready to go.
But of course, having that fixed in a backport update to 6.x would be great.
It is not fixed in the latest version. Version: 7.1.0.0.alpha0+ Build ID: a0c90f1bccd9b5a349d3199746facab549f27dba CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: ro-RO (ro_RO.UTF-8); UI: en-US TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2020-06-01_02:36:12 Calc: threaded
Sorry for the confusion. I did write it differently, that's why it worked.. A1 1; A2 2; A3 SUM(A!:A2)
Fine with 4.3.7.2
I missed the prefix part.. Excel does exactly the same thing.. so probably not a bug
It is not a bug, LibreOffice it's very strict about numbers as text, only direct references are treated as numbers, text numbers in ranges a never converted. There are some options in Menu/Tools/Options/LibreOffice Calc/Formula - Detailed calculation settings.
Created attachment 161553 [details] calc screenshot
(In reply to m.a.riosv from comment #11) > It is not a bug So it's a feature ;-) >, LibreOffice it's very strict about numbers as text, only > direct references are treated as numbers, text numbers in ranges a never > converted. Thank you for that clarification. > There are some options in Menu/Tools/Options/LibreOffice Calc/Formula - > Detailed calculation settings. Indeed, to have the same behavior, you have to check "treat as 0" in "conversion from text to numbers" (see attached calc screenshot). Thanks to all.
(In reply to m.a.riosv from comment #11) > It is not a bug, LibreOffice it's very strict about numbers as text, only > direct references are treated as numbers, text numbers in ranges a never > converted. While I have no objections against marking this as NOTABUG or WONTFIX, the above explanation is faulty because SUM(A1,A2) with direct references gives the same result (0) as SUM(A1:A2) with range reference.