Cell missed in SUM but not in +

English
#1

I found a very weird problem in a simple spreadsheet which is driving me nuts. I found the exact cell that is troublesome.

I uploaded the test spreadsheet here:
http://www.cognisys.com/w/pub/Sandbox/ErrorInLibreCalc/error%20in%20LibreOffice%20Calc.ods

I have a list of figures as follows
I will try to represent it here best I can with this limited formatting. Ignore dots.

COUNT CUM.
1…1
1…2
1…3
10…13
1…14 ← Sum using cumulative of each cell.

4 ← Sum using SUM(A2:A6)

Somehow the figure 10 is missed in the sum but not in the cumulative addition.

#2

Comment withdrawn. Misunderstood the question.

#3

In cell A5 the value is as text (cell formatted as text) not a number, such values are interpreted as number only on direct references and when they can be transformed on numbers unambiguously, but no in a range like in SUM().
+A5 and SUM(A5+A4) does but SUM(A5) or SUM(A5:A5) not.

#4

That is certainly not what I would expect. In my view, this is BAD DESIGN, If it is interpreted as a number with +, it should be the same with SUM(). I admit when I looked closer that in the edit box i could see the apostrophe denoting that it was text.

#5

Thanks for clearing this up

#6

SUM (like AVERAGE, COUNT, and a few functions in addition) is specified to ignore text independent on its content. It’s not any developer’s decision to change that. And that’s the way spreadsheets of any brand do it for deacdes now. There will be many many millions of documents the functionality of which depends on this behaviour.
Automatic conversion may even more be judged to be bad design, but that either can’t be changed for the same reasons.

#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.