From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Brand Subject: Re: sum up variables from different org-mode tables Date: Fri, 16 Nov 2012 16:16:48 +0100 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([208.118.235.92]:37972) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TZNf2-0000sO-Se for emacs-orgmode@gnu.org; Fri, 16 Nov 2012 10:16:55 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1TZNez-0004Pz-QY for emacs-orgmode@gnu.org; Fri, 16 Nov 2012 10:16:52 -0500 Received: from mail-vc0-f169.google.com ([209.85.220.169]:41233) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TZNez-0004Pn-MF for emacs-orgmode@gnu.org; Fri, 16 Nov 2012 10:16:49 -0500 Received: by mail-vc0-f169.google.com with SMTP id fl17so3504385vcb.0 for ; Fri, 16 Nov 2012 07:16:48 -0800 (PST) In-Reply-To: List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Martin Gross Cc: Org Mode Hi Martin On Fri, Nov 16, 2012 at 12:33 PM, Martin Gross wrote: > Now I would like to get some statistics that consider all institutions > together. For example summing up the "variable" TAE from all the > tables in the file to get the very total cost of outgoings. Below are two approaches to achieve this. For future development I would like to point out two things: 1) Assignment of empty field: I think that, generally and not only for your example, some existing or future format specifier should take care to not fill the destination cell with 0 when the source cell is empty. The format specifiers E and L how they work now do not help in this case. Sometimes I use a workaround like $3 =3D if($1 && $2, $1 + $2, string("")) which works only for non-zero numbers. 2) Indirection of remote table name: Although there is a very nice formula editor C-c ' (org-table-edit-formulas) which helps a lot in this case, in both variants editing of the total formula scales badly with the number of tables. A nice solution for variant 2 would be if @2$3..@2$7 =3D remote(A, @>>$$#) :: @3$3..@3$7 =3D remote(B, @>>$$#) could be simplified to @I$3..@II$7 =3D remote($8, @>>$$#) Your example with the two approaches: * Institution A #+TBLNAME: A | | In | # | =80 | Out | # | =80 | |---+---------+----+-----+---------+----+-----| | | Title P | 1 | 45 | Title A | 1 | 15 | | | | | | Title B | 2 | 28 | |---+---------+----+-----+---------+----+-----| | # | | 1 | 45 | | 3 | 43 | | ^ | | TE | TEE | | TA | TAE | #+TBLFM: $TE=3Dvsum(@I..@II)::$TEE=3Dvsum(@I..@II)::$TA=3Dvsum(@I..@II)::= $TAE=3Dvsum(@I..@II) * Institution B #+TBLNAME: B | | In | # | =80 | Out | # | =80 | |---+---------+----+-----+---------+----+-----| | | Title Q | 1 | 24 | Title C | 2 | 31 | |---+---------+----+-----+---------+----+-----| | # | | 1 | 24 | | 2 | 31 | | ^ | | TE | TEE | | TA | TAE | #+TBLFM: $TE=3Dvsum(@I..@II)::$TEE=3Dvsum(@I..@II)::$TA=3Dvsum(@I..@II)::= $TAE=3Dvsum(@I..@II) * total variant 1 | | In | # | =80 | Out | # | =80 | |---+----+----+-----+-----+----+-----| | # | | 2 | 69 | | 5 | 74 | | ^ | | TE | TEE | | TA | TAE | #+TBLFM: $TE =3D remote(A, $TE) + remote(B, $TE) :: $TEE =3D remote(A, $TEE) + remote(B, $TEE) :: $TA =3D remote(A, $TA) + remote(B, $TA) :: $TAE =3D remote(A, $TAE) + remote(B, $TAE) * total variant 2 | | In | # | =80 | Out | # | =80 | institution | |---+----+----+-----+-----+----+-----+-------------| | | | 1 | 45 | 0 | 3 | 43 | A | | | | 1 | 24 | 0 | 2 | 31 | B | |---+----+----+-----+-----+----+-----+-------------| | # | | 2 | 69 | | 5 | 74 | | | ^ | | TE | TEE | | TA | TAE | | #+TBLFM: @2$3..@2$7 =3D remote(A, @>>$$#) :: @3$3..@3$7 =3D remote(B, @>>$$#) :: $TE=3Dvsum(@I..@II) :: $TEE=3Dvsum(@I..@II) :: $TA=3Dvsum(@I..@II) :: $TAE=3Dvsum(@I..@II) =93$$#=94: the first =93$=94 is for =93column=94 and =93$#=94 is for the nu= mber of the current column to copy a row column by column, just like the equivalent =93@@#=94 mentioned in =933.5.1 References" in the manual. Michael