Simple sum in column getting zero in libreoffice calc 5.

users
#1

Hi all,

I was trying a simple addition in column =SUM(B2:B8)

but that didn't work . I am getting the output as zero.

Could somebody look at it and share what is/was wrong ?

Sharing an example worksheet for the same.

#2

shirish शिरीष wrote:

> Hi all,
>
> I was trying a simple addition in column =SUM(B2:B8)
>
> but that didn't work . I am getting the output as zero.
>
> Could somebody look at it and share what is/was wrong ?
>
> Sharing an example worksheet for the same.
> --

The mailing list doesn't accept attachments. You could use Nabble for that (http://www.libreoffice.org/get-help/nabble/)

The simplest explanation would be that your "numbers" are entered as text. Check your cell format.

#3

Check cell formats and contents in B2:B8. SUM() will not add numbers
within cells formatted as text or numbers preceded by a half quote. Per
below the sum() result is 4 as such items are not summed.

4 '=SUM(B2:B8)
2 Cell number format with '2 (note the half quote)
3 Cell number format with '3 (note the half quote)
5 Cell formatted as text.
1 Cell number format with 1
1 Cell number format with 1
1 Cell number format with 1
1 Cell number format with 1

#4

at bottom :-

<snipped>

The mailing list doesn't accept attachments. You could use Nabble for that
(http://www.libreoffice.org/get-help/nabble/)

The simplest explanation would be that your "numbers" are entered as text.
Check your cell format.
--
Piet van Oostrum <piet@vanoostrum.org>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]

Dear Piet,

Yours and a private mail are bang on target. Libreoffice thinks that
those 'numbers' are text. This I was able to find via View > Value
highlighting .

Now non-plussed how to tell libreoffice to tell it to treat them as
'real numbers' ?

#5

​Short answer: it should work :slight_smile:

If a cell is somehow stuck in the "text" type, you could try clearing all
formatting on this cell (defaults to Ctrl-M), or select the affected cells,
do right-click/properties, then select an appropriate number format.
However, LibreOffice should recognize number automatically unless
explicitely instructed to treat them as text; this can happen from weird
copy/paste, when opening files in exotic formats, or by doing it by hand.
It is also possible (although we didn't see a sample file) that there are
extra characters in the cells, or maybe an invalid decimal separator for
the file's locale.

​If you could upload a sample somewhere, we might get a better look at it
:)​

#6

shirish शिरीष wrote:

> Dear Piet,
>
> Yours and a private mail are bang on target. Libreoffice thinks that
> those 'numbers' are text. This I was able to find via View > Value
> highlighting .
>
> Now non-plussed how to tell libreoffice to tell it to treat them as
> 'real numbers' ?

One way you can get numbers as text is when you import a CSV file, and forget to merk the column as numbers. Or when you paste from plain text and forget the same.

One way to get them as numbers is to add a function in another column, containing
=NUMBERVALUE(B2)
if the first number is in cell B2.
Then copy this down for all numbers in the original column. Now you have a copy of your numbers, and they are really formatted as numbers. Now Copy this column with Edit > Copy and do a Edit > Paste Special over the original "text numbers", selecting only "Numbers".

#7

Also, format the column as numbers. That way when you enter a number it is recorded as such. If the column is formatted as text, and you input a number it is interpreted as a character.

#8

Hi,

Now non-plussed how to tell libreoffice to tell it to treat them
as 'real numbers' ?

One way you can get numbers as text is when you import a CSV file,
....

This is another option:
http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

Kind regards,
Cor

#9

Ok; having seen the file from a direct message, the problem is way simpler
than a formatting/data type problem :slight_smile:

The SUM() function works to add values from cells. The problem is that you
have something in your cell that is not *a* number (things like
"3,94,700"). There is no way (that I know of...) to interpret that as a
number, so it is seen as a string.

Since the sample file seems to have three values in each cell, I assume
that the goal is to sum multiple tuples of "x,y,z" to get, in the end, the
sum of three series of values.
You can (and should...) put each group of values in separate columns and
sum them separately.

If your input data comes from a CSV file, make sure that the comma is used
as a separator, so the input gets split into separate columns. It is the
best course of action IMO; if you need the output in a specific format it
is always possible to merge the results.

I upload the sample file so people can get a look at it:
https://www.dropbox.com/s/qm03gjw3wwlpby9/example.ods?dl=0

Anything more would require knowledge of where your input comes from, or
what kind of output you need.

#10

... having seen the file from a direct message, the problem is way simpler than a formatting/data type problem :slight_smile:

No, it *is* a problem of data format and interpretation.

The problem is that you have something in your cell that is not *a* number (things like "3,94,700").

But those *are* numbers - just expressed according to the Indian numbering system; see
https://en.wikipedia.org/wiki/Indian_numbering_system .

There is no way (that I know of...) to interpret that as a number, so it is seen as a string.

Such numbers are interpreted correctly (and can be displayed similarly) if the cells are formatted according to an appropriate language, that language either inherited from the locale or set explicitly in the Format Cells dialogue. LibreOffice is cleverer than you think!

Brian Barker

#11

​Well, I was familiar with quite a few ways to represent numbers, but not
that one. We learn everyday it seems. So it comes back to what was said
previously, setting the correct locale for the cells.​

#12

Oh, join the club! For what it's worth, I wasn't familiar with this representation either - nor was I aware that LibreOffice could cope with it. But there was just enough of a memory in the back of my mind to send me to look it up and experiment ...

Brian Barker