Ask Your Question
1

Database functions in calc, impossible to create larger tables? [closed]

asked 2014-04-16 19:40:52 +0200

RHPV gravatar image

updated 2015-09-08 20:39:38 +0200

Alex Kemp gravatar image

Hi,

I am using LibreOffice 3.5.7.2 on Ubuntu 12.04.4 LTS.

I set up a database in calc and it is ordered the following way:

Code \ Plaatscode \geoghrapic data\ Date1\ Date2 \ Date3 etc.

NDC01\NDC \ geographic data \ number \ number \ number etc.

NDC02\NDC \ geographic data \ number \ number \ number etc.

NDC03\NDC \ geographic data \ number \ number \ number etc.

NHE01\NHE \ geographic data \ number \ number \ number etc.

etc.

So the idea is: Location (three letters), point at that location (the three letters + two numbers).

I want to take the sum of all the points per location. This is possible with the DSUM function. I tried to integrate it as follows

Plaatscode / Date 1 /date2 etc.

NDC/ formula

NHE/

Formula: =DSUM(database;column refers to date _the column heading_ ; search criteria)

The last part with the search criteria is where it al goes wrong. I first typed A1:A2. So it gets: plaatscode and NDC. This works perflecty fine. But then I want Plaatscode and NHE. When I drag and drop I get A2:A3 (NDC, NHE) which does not work and when I use the $ symol I get A$1:A3 (Plaatscode,NDC,NHE). This gives me the sum of the data of all NDC and NHE entries (which makes sense). There seems to be no way I can type that I want just A$1 AND A3 as entry for the search criteria.

I have tried arrays and the concatenation symbol to get A1 and A3 together but nothing works to get a nice table.

My current non-perfect solution is to alternate plaatscode and the actual code in the column so i get: plaatscode/ndc/plaatscode/nhe/plaatscode/etc. But this looks awful and is annoying later on.

My next problem is going to be getting the average out of the dates based on which month they are in. I expect similar problems, but I haven't tried yet because it is essentially the same problem as I am facing now.

I read the manuals, searched ask.libreoffice, looked at the wiki and did this several times until I decided that the answers is not yet out there. Can anyone help me figure this out?

Kind regards, Ruben

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 21:11:51.420730

Comments

Care to upload an example file? I don't think I can follow you here without one.

mahfiaz gravatar imagemahfiaz ( 2014-04-16 22:28:35 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-04-16 23:17:45 +0200

m.a.riosv gravatar image

I think better use SUMIF(Range; Criteria; SumRange) function, seems to me more appropriated on how you want to do.

SUMIF help

edit flag offensive delete link more

Comments

Sumif worked perfectly! Thanks a lot! Shame there is no averageif function, but I managed with sumif and countif in conjunction. Thanks a lot again!

RHPV gravatar imageRHPV ( 2014-04-17 13:25:40 +0200 )edit

From 4.x there are AVERAGEIF() and AVERAGEIFS() functions.

m.a.riosv gravatar imagem.a.riosv ( 2014-04-17 15:51:24 +0200 )edit

Thanks again :-). I will get to a rolling release of linux ASAP, so I can get the latest and greatest of libreoffice.

RHPV gravatar imageRHPV ( 2014-07-10 23:58:52 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-04-16 19:40:52 +0200

Seen: 94 times

Last updated: Apr 16 '14