From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?utf-8?Q?=C5=81ukasz?= Stelmach Subject: Re: Howto define formula for table regions Date: Wed, 22 Sep 2010 15:04:42 +0200 Message-ID: <87fwx26iad.fsf@dasa3.iem.pw.edu.pl> References: <4C996791.1070403@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Return-path: Received: from [140.186.70.92] (port=58117 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1OyP0S-0001Og-Js for emacs-orgmode@gnu.org; Wed, 22 Sep 2010 09:05:05 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1OyP0Q-0007aF-Ou for emacs-orgmode@gnu.org; Wed, 22 Sep 2010 09:05:04 -0400 Received: from lo.gmane.org ([80.91.229.12]:49784) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1OyP0Q-0007ZZ-F0 for emacs-orgmode@gnu.org; Wed, 22 Sep 2010 09:05:02 -0400 Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1OyP0L-0007Ug-N3 for emacs-orgmode@gnu.org; Wed, 22 Sep 2010 15:04:57 +0200 Received: from dasa3.iem.pw.edu.pl ([194.29.147.110]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 22 Sep 2010 15:04:57 +0200 Received: from lukasz.stelmach by dasa3.iem.pw.edu.pl with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 22 Sep 2010 15:04:57 +0200 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 Torsten Wagner writes: > 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 | OK. Let's rebuild the table a bit |---+------+------+-----------------------+--------+--------+--------| | ! | 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 | | | | | 0 | |---+------+------+-----------------------+--------+--------+--------| | | | | balance on account | | | 500 | | ^ | | | | | | bal | |---+------+------+-----------------------+--------+--------+--------| | # | 1 | | outstanding order | 1 | -100 | -100 | | # | 2 | | 2. payment to crew | 3 | -100 | -300 | |---+------+------+-----------------------+--------+--------+--------| | | | | balance - outstanding | | | 100 | | ^ | | | | | moveup | outbal | |---+------+------+-----------------------+--------+--------+--------| The special characters in the first columns make some difference. With `!' you get names for columns you may use in your formulae (you'll see below). `#' forces recalculation of selected rows upon any Tab or Return keypress, and `^' gives names to cells in the row above. You can find more about the special characters for the first column in chapter. These changes aren't a must but they make formulae a bit more readable. > 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. Be not afraid, it's easier than you think. Simply do what FAQ says but enter :=1 in first lines of every block. If we consider the table above: B2 = 1 B8 = 1 B& = @-1 + 1 is everything you need[*]. > 2. Place the multiplication of "number" with "amount" in the sum > column This is the easiest, just follow the manual and write $7 = $number * $amount > 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) This isn't as hard as it seems too: bal = vsum(@-II..@-I) outbal = bal + vsum (the numbers suggest outbal includes bal am I right). > 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 ..." There is no need for such a thing since cell formulae "cover" column ones. > 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 This one *is* tricky. You won't achieve it without lisp (at least IMHO). OK, let's try: + I'd use an empty cell to put a Lisp formula in it: moveup. + The value it returnes isn't as important as its side effects: table rearrangement. + Let's use org timestamps: <2010-09-22 śro> (C-c C-.), so we can use org-parse-time-string function. [...] Phew, that was a *thing* but here you are: --8<---------------cut here---------------start------------->8--- (save-excursion (let* ( (hlineA (1+ (org-table-get-descriptor-line "-II"))) (hlineB (org-table-get-descriptor-line "-I") ) (curline hlineA) theline) (while (< curline hlineB) (org-goto-line curline) (org-table-goto-column 3) (when (time-less-p (apply 'encode-time (org-parse-time-string (org-no-properties (org-table-get-field)))) (current-time) ) (dotimes (i 4) (setq theline (append theline (list (org-table-get-field)))) (org-table-next-field)) (org-table-kill-row) (org-goto-line (1- (org-table-get-descriptor-line "III"))) (org-table-goto-column 3) (org-table-next-row) (dolist (col theline) (insert col) (org-table-next-field)) (setq curline (1- curline)) (setq theline nil) ) (setq curline (1+ curline)))) "") --8<---------------cut here---------------end--------------->8--- and put this as: $moveup = '(save-excursion .... blah blah ..) It should be possible to cut it down by about 20% but I leave it as an exercise to someone else ;-) You could also try the feature which enables simple calculations too plus you can keep your numbers in the tree structure with more elaborate info on them. You could then use agenda views to select and sum up entries by dates too. [*] I've copied all the formulae from the formula buffer which you activate with C-' while cursor is in a table. This is very convenient way of entering formulae since you get the cells you reference highlighted. -- Miłego dnia, Łukasz Stelmach