Created attachment 123145 [details] spreadsheet with example of incorrect sum calculation If initial cells' type is text, sum(a1:a6) and a1+a2+a3+a4+a5+a6 gives different results in attached spreadsheet - please see b1 and b2. Changing of cells type to numeric does not help.
hi, vadimir. this numbers in column A you type manual or pasted it from another document?
Hi. Unfortunately, the original document is rather old, and I could not say for sure. Numbers into this example were pasted from original document; but moreover - if you manually type the same (or even other) numbers into these cells over existing ones, result will be the same.
if you paste this number from another document, then use paste-paste special-formatted text (RTF). russian - (Вставить-Вставить как...-Форматированный текст [RTF]) or select all cells with numbers, make format cell as Number and delete apostrophe before numbers. IMHO, this is not a bug. Function Sum calculates the sum of the numbers and the text - it is not a number
But why then two types of formulae give different results? Why a1+a2 calculates the sum of a "text" cells?
Could this be (related) to bug 97897.
Bodhi Moksha Version: 5.2.0.0.alpha0+ Build ID: aaca25d67eb5ea252730cdcf555ecc04ce04a5e6 CPU Threads: 2; OS Version: Linux 3.16; UI Render: default; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-02-24_23:58:47 Locale: en-US (en_US.UTF-8) I am marking this as NEW for a couple reasons: NEW Normal - can prevent high quality work in certain circumstances Low - lowering due to the below There is indeed something funky with the data and it is in fact text. You can confirm this by installing ct2n extension and then convert the text to actual numbers, do a recalculate (f9) and things work. That being said, the fact that SUM fails but you can add them together independently is funky behavior. It should be consistent, either a consistent fail, or a consistent "treat text like number". Adding Eike to see his thoughts.
This is not funky behavior, the SUM function as many other functions that take range parameters for numeric calculations processes range arguments as number sequences, ignoring all text values. This is also defined in OpenFormula (ODFF) and also what Excel does. The result of A1+A2 actually depends on the setting of "Conversion from text to numbers", under Tools->Options->Calc->Formulas "Detailed Calculation Settings", choose Custom, Details. I recommend the "Generate #VALUE! error" setting so all numeric calculation attempts with text instead of numbers generates an error to more easily detect odd cell content.
(In reply to Eike Rathke from comment #7) > This is not funky behavior, the SUM function as many other functions that > take range parameters for numeric calculations processes range arguments as > number sequences, ignoring all text values. This is also defined in > OpenFormula (ODFF) and also what Excel does. > > The result of A1+A2 actually depends on the setting of "Conversion from text > to numbers", under Tools->Options->Calc->Formulas "Detailed Calculation > Settings", choose Custom, Details. I recommend the "Generate #VALUE! error" > setting so all numeric calculation attempts with text instead of numbers > generates an error to more easily detect odd cell content. totally agree
Thanks for clarification. The only point - in my opinion it will be good, if this option ("Conversion from text to numbers") would be set to the "Generate #VALUE! error" by default.
(In reply to vladimir.kozlov from comment #9) > Thanks for clarification. The only point - in my opinion it will be good, if > this option ("Conversion from text to numbers") would be set to the > "Generate #VALUE! error" by default. make a new bug with Importance -> enhancement
After some tests, my opinion was changed: suggested change of "Conversion from text to numbers" value does not solve the problem. Imagine column with all numeric cells but one or couple erroneously text ones. In this case formulae A1+...+A6 will give error(and that is good), but SUM(A1:A6) will give just "incorrect" result without any warning, so user could not always trust the SUM formulae. So the point is the same - why these two formulae give different results? I do understand that SUM just ignores text values, but is it possible to force it to give an error in this case?
Hi Vladimir, Did you check out the options in Tools > Options > Calc > Formula .. Detailed Calculation Settings... [More] ?
Yes, there are six options, but none of them forces SUM formulae to generate error if one of cells is of text type.
(In reply to vladimir.kozlov from comment #13) > Yes, there are six options, but none of them forces SUM formulae to generate > error if one of cells is of text type. Please understand that SUM (and other functions that take a range argument to evaluate as numeric sequence) *never* calculates with cells of type text and *always* ignores cells of type text. This in any major spreadsheet application.