sum function in libreoffice calc doesn't seem to work

users
#1

To Whom It May Concern:

I don't want to sound nuts or something, but I just in stalled LibreOffice 3.3.0
(build 3.2.99.2)and when I attempt to do a =SUM function on a column in
a
spreadsheet, it doesn't work. It won't recognize the range of cells
defined by the
mouse. And when I manually insert the range (i.e., =SUM(C2,C46))
it returns a
result of 0 (zero).

Shouldn't a working =SUM function be one of the basic elements
of a spreadsheet?

I'm sure it's my fault. Could someone please explain what
I'm doing wrong?

Sincerely/Bill Woodruff

#2

A sum range is =SUM(C2:C46) - could that be (part of) it?

Mark

#3

In my installation all works correctly... don't use comma but semicolon between parameters.

#4

Semicolon? No, it should be a colon.

#5

Hi,

Semicolon? No, it should be a colon.

Well this depends on what you aim for.

=SUM(A1:B10) sum up all from A1 to B10
=SUM(A1;B10) sum A1 and B10

Stefan

#6

Your both right, based on OS.

#7

Actually just ran a quick test on Ubuntu and WinXP. The ':' is used for a range (a1:a4) a ';' is changed to a ',' for two cells (a1,a4).

Andy

#8

Actually it depends on your Language Settings.

And LibreOffice inherited the problems from OpenOffice...

E.g. for Portuguese (European) the decimal separator is a comma(,) but if I
prefer to use a point (because most international publications are formatted
according to US notation) when I disable the box "Same as locale setting" it
will type the point and as soon as I press enter it will convert it to a
date. This is a clear mistake since a point is not a separator in
Portugal... i.e. typing 2.3 is converted to 02-03-2010 which is value 40239

#9

Am 03.11.2010 23:12, bill woodruff wrote:

mouse. And when I manually insert the range (i.e., =SUM(C2,C46))
  it returns a
result of 0 (zero).

You try to sum numeric text (a sequence of digits).

#10

Mark <mhullrich <at> gmail.com> writes:

>
A sum range is =SUM(C2:C46) - could that be (part of) it?

Mark

Hello, Mark:

Thank you for your prompt reply. I apologize, but I mis-punctuated
my original message. The formula, as you correctly observe, is =SUM(C2:C46)
--and that's pasted directly from the spreadsheet.

However, it still returns a result of 0 (zero).

I've been working with spreadsheets (mostly Excel) since the days of
Lotus Symphony. I had high hopes for LibreOffice and have had good
results with the document package so far. But this spreadsheet behavior
is driving me nuts.

I would appreciate any suggestions.

Would you like me to e-mail you a copy of the spreadsheet?

Sincerely/Bill Woodruff

#11

Bill, do the cells in the range contain only natural numbers?

I suspect that you are having a problem with the decimal separator.

Your cells are probably identified as text because of that (are the values
aligned to the left?)

Paste this in cell D2 to check
=VALUE(C2)

#12

+1
Perhaps an imported files with " ' " in front of each number: you don't see them in the sheet (let's say you have "123" in C3) but by clicking in it you can see: '123 in the "input line".

#13

Am 06.11.2010 17:16, gilles wrote:

Am 03.11.2010 23:12, bill woodruff wrote:

mouse. And when I manually insert the range (i.e., =SUM(C2,C46))
it returns a
result of 0 (zero).

You try to sum numeric text (a sequence of digits).

+1
Perhaps an imported files with " ' " in front of each number: you don't
see them in the sheet (let's say you have "123" in C3) but by clicking
in it you can see: '123 in the "input line".

OK, then you should import numbers rather than text. If you import or enter text into your spreadsheets, Calc will treat it as text and no formatting will convert your values or modify them by any means.
When you re-enter all those values you see that they become numbers. Ctrl+F8 will highlight them in blue, they become right bound, they will be summed.
There is a semi-automatic method to re-enter all formulas of a selection:
Select the cells in question, apply any non-text number format and some appropriate number format locale (e.g. German for 1,99 decimals, English for 1.99 decimals, US English for 12/31 dates).

Find&Replace...

[More Options]
[x]Current Selection
[x]Regular Expressions
Search: .+
Replace: &
[Replace All]
The same method converts numbers to text when you apply a text formatting before the replacment.

#14

plino <pedlino <at> gmail.com> writes:

Bill, do the cells in the range contain only natural numbers?

I suspect that you are having a problem with the decimal separator.

Your cells are probably identified as text because of that (are the values
aligned to the left?)

Paste this in cell D2 to check
=VALUE(C2)

PLino:

Thank you very much for your reply.

Yes, the figures are aligned left. They were imported from a .csv file.
However, attempts to re-format the column as currency fail to to anything
except to add an apostrophe ('), which I believe denotes "text",
to the front of each entry. If I manually access each cell, I can
delete the apostrophe ... but this is way too much work.

I have tried to re-import the .csv file, but there are no options
(which I can see) to designate a specific column as currency
(or some other format).

I would appreciate further suggestions. I was a big fan of OpenOffice
and I have high hopes for LibreOffice ... but these spreadsheet
difficulties are not helping me.

Sincerely/Bill Woodruff

#15

plino <pedlino <at> gmail.com> writes:

Bill, do the cells in the range contain only natural numbers?

I suspect that you are having a problem with the decimal separator.

Your cells are probably identified as text because of that (are the values
aligned to the left?)

Paste this in cell D2 to check
=VALUE(C2)

Hello, Plino:

This is a second reply. After I wrote you earlier, I tried re-importing
the .csv file. This time, I clicked on the "Standard" heading on top
of the column in question, hoping there would be a format for "Currency."
There was none, but after I clicked "Hidden" then clicked back to
"Standard," the file imported with the currencies properly recognized
and aligned to the right.
Now the =SUM(c2:c46) function works just fine. I am sorry for wasting
so many people's time, but I could not figure this out by myself. I am
very grateful to everyone involved for their suggestions.
One final question: Shouldn't the Import function recognize currencies
automatically rather than making the user jump through these hoops?

Sincerely/Bill Woodruff

#16

So after some experimentation I've hit upon an easy fix for importing CSV
files with numbers ... changing the text delimiter to ' and selecting
"Quoted field as text" and "Detect special numbers" does the trick. Of
course you have to save the file before you can SUM or do anything else.

The nice thing is once you change these settings once they become the
default setting.

#17

I'm having this same problem and I must admit, this is a deal breaker for
LibreCalc for me and possibly for Linux since I can't use VMWare on kernel
3.0.o.1.

Going to try Google Docs but I am stunned that LibreOffice makes you jump
through so many hoops to using the SUM function on a column of numbers
imported from CSV.

#18

Hi :slight_smile:
Different devices or programs might decide to use different ways of defining
what a column contains and might also vary as to whether it treats a
particular column as a number or as text.

Typically i would open a Csv file with a text-editor such as Scite, Gedit or
even Notepad (if that's all i have) just to have a look at how the info is
presented. Search&replace is often useful.
Regards from
Tom :slight_smile:

#19

Get the Extension CT2N, which is convert text2 Numbers.
You only jump through one loop then.

Tink.

#20

Bravo! This was just the solution/explanation I needed!

joshsimpson <josh.simpson <at> gmail.com> writes: