[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [O] sum up variables from different org-mode tables
From: |
Michael Brand |
Subject: |
Re: [O] sum up variables from different org-mode tables |
Date: |
Fri, 16 Nov 2012 16:16:48 +0100 |
Hi Martin
On Fri, Nov 16, 2012 at 12:33 PM, Martin Gross <address@hidden> 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 = 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
@address@hidden = remote(A, @>>$$#) :: @address@hidden = remote(B, @>>$$#)
could be simplified to
@address@hidden = remote($8, @>>$$#)
Your example with the two approaches:
* Institution A
#+TBLNAME: A
| | In | # | € | Out | # | € |
|---+---------+----+-----+---------+----+-----|
| | Title P | 1 | 45 | Title A | 1 | 15 |
| | | | | Title B | 2 | 28 |
|---+---------+----+-----+---------+----+-----|
| # | | 1 | 45 | | 3 | 43 |
| ^ | | TE | TEE | | TA | TAE |
#+TBLFM:
$TE=vsum(@address@hidden)::$TEE=vsum(@address@hidden)::$TA=vsum(@address@hidden)::$TAE=vsum(@address@hidden)
* Institution B
#+TBLNAME: B
| | In | # | € | Out | # | € |
|---+---------+----+-----+---------+----+-----|
| | Title Q | 1 | 24 | Title C | 2 | 31 |
|---+---------+----+-----+---------+----+-----|
| # | | 1 | 24 | | 2 | 31 |
| ^ | | TE | TEE | | TA | TAE |
#+TBLFM:
$TE=vsum(@address@hidden)::$TEE=vsum(@address@hidden)::$TA=vsum(@address@hidden)::$TAE=vsum(@address@hidden)
* total variant 1
| | In | # | € | Out | # | € |
|---+----+----+-----+-----+----+-----|
| # | | 2 | 69 | | 5 | 74 |
| ^ | | TE | TEE | | TA | TAE |
#+TBLFM: $TE = remote(A, $TE) + remote(B, $TE) :: $TEE = remote(A,
$TEE) + remote(B, $TEE) :: $TA = remote(A, $TA) + remote(B, $TA) ::
$TAE = remote(A, $TAE) + remote(B, $TAE)
* total variant 2
| | In | # | € | Out | # | € | institution |
|---+----+----+-----+-----+----+-----+-------------|
| | | 1 | 45 | 0 | 3 | 43 | A |
| | | 1 | 24 | 0 | 2 | 31 | B |
|---+----+----+-----+-----+----+-----+-------------|
| # | | 2 | 69 | | 5 | 74 | |
| ^ | | TE | TEE | | TA | TAE | |
#+TBLFM: @address@hidden = remote(A, @>>$$#) :: @address@hidden = remote(B,
@>>$$#) :: $TE=vsum(@address@hidden) :: $TEE=vsum(@address@hidden) ::
$TA=vsum(@address@hidden) :: $TAE=vsum(@address@hidden)
“$$#”: the first “$” is for “column” and “$#” is for the number of the
current column to copy a row column by column, just like the
equivalent “@@#” mentioned in “3.5.1 References" in the manual.
Michael