0

sharing as an example. I have data starting from column b2 to b8 where I want to add the values (sum) and get the output in another cell, say b10.

The most logical way should have been =SUM(B2,B8) but using that formula I get the output as 0. I tried various other options, ways but get different error numbers. I used to do similar in Excel way back and that used to work. What am I doing wrong ?

Update: Doing =SUM(B2:B8) also gives 0 (zero), same as before.

Update 2 :- It seems (for some reason) libreoffice is thinking/seeing the number as text values. I had put the same query in libreoffice mailing list and one of the private mails said to use View > Value Highlighting and sure enough all were in black which means libreoffice thinks that the numbers are text values. I didn't get any answer as to how to change from text values to 'true numbers'. The numbers are like 3,94,700 and so on and so forth.

shirish
  • 11,967
  • 27
  • 107
  • 190
  • `=SUM(B2,B8)` sums up just the values in cells B2 & B8 only; see the answer below to use the correct separator ":" to indicate a range of cells from B2 through B8. – Mark Stewart May 11 '16 at 15:07
  • 1
    Is there any chance you could share the worksheet you're using? – Stephen Kitt May 11 '16 at 15:13
  • 2
    I'm voting to close this question as off-topic because question has no UNIX reference., It is purely a libre office calc related question. – MelBurslan May 11 '16 at 15:35
  • if you know of any anonymous file sharing site, please let me know. I'm not going to sign up to some file-sharing site just for the sake of one spreadsheet. – shirish May 11 '16 at 15:36
  • @MelBurslan , are you saying that libreoffice is not part of unix ? – shirish May 11 '16 at 15:37
  • @shirish it is not a **PART** of Unix. It is a stand alone software, capable of running on many OSes including windows. And your question has no relevance to the UNIX or Linux OS. You are asking a question about how to formulate cell contents. – MelBurslan May 11 '16 at 15:40
  • Then which site would be good for it, stackoverflow is for programming, is there any site within the stack sites where libreoffice questions would be welcomed ? – shirish May 11 '16 at 15:45
  • Also how then http://unix.stackexchange.com/questions/239303/calc-how-to-convert-a-numeric-column-in-place was allowed as it is a similar question and nothing to do with unix per-se. I saw those and similar questions and then only put it up. – shirish May 11 '16 at 15:48
  • while there *may* be more LibreOffice experts at, e.g. [SuperUser](http://superuser.com), (326 questions for libreoffice-calc alone, vs 89 *total* libreoffice questions at U&L), the question of [U&L scope](http://meta.unix.stackexchange.com/questions/97/offtopic-a-stance-programming-servers-cross-platform-applications) has come up before. – Jeff Schaller May 11 '16 at 16:41
  • 1
    What precisely is in cells B2 through B8? – Jim K May 11 '16 at 17:14
  • 1
    @shirish "Applications packaged in *nix distributions (note: being [cross-platform does not disqualify](http://meta.unix.stackexchange.com/q/97/29))" is explicitly on-topic here per the [Help Center's On-Topic section](http://unix.stackexchange.com/help/on-topic) – derobert May 11 '16 at 18:05
  • @JimK have edited the question so you know more. – shirish May 11 '16 at 19:25
  • LibreOffice may be on-topic here, but http://superuser.com/ would be a much better SE site for questions about it. – cas May 12 '16 at 01:44

3 Answers3

3

You need to specify the range:

=SUM(B2:B8)

(in B10).

As explained by Mark Stewart, SUM(B2,B8) is equivalent to B2+B8 — you'd use that form of SUM() if you had a specific list of disjoint cells to add (or disjoint cell ranges).

Community
  • 1
Stephen Kitt
  • 411,918
  • 54
  • 1,065
  • 1,164
1

Try this:

  1. In cell C2 enter 2 and in cell C3 enter 3.
  2. In cell C4 enter =SUM(C2:C3).

The result (which I tested to make sure) should show as 5.

If this worked, then check what is in B2 through B8 to make sure that they are all numbers and that they add up to an integer other than zero.

For example, one way to end up with 0 is by summing 0.1 and 0.2 and then formatting the result as an integer. It will be rounded down to zero. To see if this may be the problem, go to Format -> Cells and see what format is being used.

Also, make sure that the values in the cells are numbers, not strings. In contrast to a language like Perl, summing '1' and '2' results in 0 in Calc.

EDIT:

Since it sounds like the numbers have somehow been entered as text, you can get their numeric values by using INT(). For example:

  1. In cell D2 enter ="5", which results in a string. (Typing simply '5 should be a text value as well, but this did not seem to work on my version of LibreOffice.)
  2. In cell D3 enter =INT(D2) to get the numeric value.

You can then use D3 in sums.

Jim K
  • 591
  • 2
  • 6
0

Try switching the commas , in your numbers to dots .. LibreOffice probably expects to see dots because the information from your pc is telling it that in the chosen country, dots are used to separate the thousands and commas define the decimal point, so it thinks your numbers have multiple decimal points and since this is not possible it sees them as text.

In most of europe (I think) numbers are formatted like this 578.120,5. I am in europe and had pretty much the same problem as you.

P.S You can see what libre office thinks the value of a cell is be going to View and enabling Value Highlighting. The values shown in black are text whereas blue means the value is a number.

Jeff Schaller
  • 66,199
  • 35
  • 114
  • 250
Thanasis Malasidis
  • 1