From mboxrd@z Thu Jan 1 00:00:00 1970 From: Torsten Wagner Subject: Howto define formula for table regions Date: Wed, 22 Sep 2010 11:18:57 +0900 Message-ID: <4C996791.1070403@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Return-path: Received: from [140.186.70.92] (port=58518 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1OyEv6-0008V7-MR for emacs-orgmode@gnu.org; Tue, 21 Sep 2010 22:18:54 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1OyEv5-0002gW-6R for emacs-orgmode@gnu.org; Tue, 21 Sep 2010 22:18:52 -0400 Received: from mail-pz0-f41.google.com ([209.85.210.41]:65040) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1OyEv5-0002gG-1w for emacs-orgmode@gnu.org; Tue, 21 Sep 2010 22:18:51 -0400 Received: by pzk26 with SMTP id 26so42967pzk.0 for ; Tue, 21 Sep 2010 19:18:49 -0700 (PDT) List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org Hi, I am just playing around with the table function of org-mode. I tried to create a little table to track the financial status of a project. I thought it would be nice to distinguish between entries which really had billed the account already and entries which will bill the account in the future. This would allow me to track and compare the current amount of my project account and in addition makes sure I will not overdraw my account by missing an outstanding transaction. Thus, initially my idea was to create a table like this: |-----+------+-----------------------+--------+------------+------| | nr. | date | title | number | amount | sum | |-----+------+-----------------------+--------+------------+------| | 1 | | inital fund | 1 | 1000 | 1000 | | 2 | | 1. payment to crew | 3 | -150 | -450 | | 3 | | 1. order | 1 | -50 | -50 | | 4 | | | | | | |-----+------+-----------------------+--------+------------+------| | | | balance on account | | | 500 | |-----+------+-----------------------+--------+------------+------| | 1 | | outstanding order | 1 | -100 | -100 | | 2 | | 2. payment to crew | 3 | -100 | -300 | |-----+------+-----------------------+--------+------------+------| | | | balance - outstanding | | | 100 | |-----+------+-----------------------+--------+------------+------| Now I would like to add formulas for: 1. Increasing the number of the first column ("Nr."), (entry in the FAQ, I know), whenever I add a new row. However do this for two blocks and leave row 6 and the last row empty. 2. Place the multiplication of "number" with "amount" in the sum column 3. Add the total sums of the two blocks in row 6 and the last row (taking into account that the table will grow over time) I know about column formulas and field formulas but I did not find a way to do something like "for each field in column X between row a and row b calculate ..." I thought I could use the hline notation to encapsulate a undefined amount of rows in between, however this doesn't work since every formula does only refer to one field or to an entire column. What I am looking for is something like: $6@II+1..$6@III-1 = $4@0 * $5@0 or in text: for each field in column 6 starting with the first row below the second hline and end with row above the third hline multiply the column 4 and column 5 of the current row. Is there any other way to achieve this? Or does this make much enough sense to implement it ;) I guess there might be a lisp-way but I think a more intuitive way for non-lisper might be useful. As a kind of extra....for the very org-mode pros on this list If I fill dates into the date column, a mechanism which moves automatically (in the same way as I refresh calculation of formulas), the rows from the second block into the first block when the date passed would be some nice gadget Best regards Torsten CC. The manual says, the very first hline will not be counted for the hline referencing notation. However, as far as I can see it does count in both formulas and by indicating it with C-} (Org-mode version 6.36trans (release_6.36.437.g05b7e))