From mboxrd@z Thu Jan 1 00:00:00 1970 From: Nick Dokos Subject: Re: making flexible table formulas Date: Tue, 01 Mar 2011 00:10:11 -0500 Message-ID: <1742.1298956211@alphaville.dokosmarshall.org> References: Reply-To: nicholas.dokos@hp.com Return-path: Received: from [140.186.70.92] (port=39332 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PuI4c-0008Jn-Cm for emacs-orgmode@gnu.org; Tue, 01 Mar 2011 00:24:39 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PuI4b-00008y-3D for emacs-orgmode@gnu.org; Tue, 01 Mar 2011 00:24:38 -0500 Received: from vms173003pub.verizon.net ([206.46.173.3]:35448) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PuI4a-000085-SU for emacs-orgmode@gnu.org; Tue, 01 Mar 2011 00:24:37 -0500 Received: from alphaville.dokosmarshall.org ([unknown] [173.76.32.106]) by vms173003.mailsrvcs.net (Sun Java(tm) System Messaging Server 7u2-7.02 32bit (built Apr 16 2009)) with ESMTPA id <0LHD007KK5O850F0@vms173003.mailsrvcs.net> for emacs-orgmode@gnu.org; Mon, 28 Feb 2011 23:24:21 -0600 (CST) In-reply-to: Message from Rustom Mody of "Tue, 01 Mar 2011 08:49:40 +0530." 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: Rustom Mody Cc: nicholas.dokos@hp.com, emacs-orgmode Rustom Mody wrote: > Nick Dokos wrote: > > > Of course it's reasonable - and of course, org implements it > > #+TBLFM: $LR2=vsum(@1..@-1) > > Thanks Nick I can use that. But I dont understand it. What's the -1? > The manual says -- relative to 'current' column. > What determines 'current?' There must be some obvious POV which I am missing... > The formula calculates $LR2: the cell in the last row, column 2. That's what determines 'current'. Since there is no column spec on the right-hand side, column 2 is implied. IOW, the formula is equivalent to this: #+TBLFM: $LR2=vsum(@1$2..@-1$2) The vsum on the RHS ranges then from row 1, column 2 (@1$2) to the row above the last one, still on column 2 (@-1$2). BTW, if you have a header you should start at row 2 (separator lines don't count): | A | B | |-------+----| | 1 | 1 | | 2 | 4 | | 3 | 9 | | Total | 14 | #+TBLFM: $LR2=vsum(@2..@-1) or use the alternative syntax that Luke Crook suggested [fn:1] #+TBLFM: $LR2=vsum(@I..@-1) which goes from the first separator (@I) to the penultimate row (@-1) - and remember that separator lines are ignored in the calculation. HTH, [fn:2] Nick Footnotes: [fn:1] Luke suggested the following formula: #+TBLFM: @8$2=vsum(@I..@II) with the assumption that there will be two separator lines, one after the header and one just before the last row, in effect demarcating the useful part of the table: | A | B | |-------+----| | 1 | 1 | | 2 | 4 | | 3 | 9 | |-------+----| | Total | 14 | #+TBLFM: $LR2=vsum(@I..@II) [Luke's LHS fixes the row to 8 which is not correct - I corrected that in the above formula.] The only problem with this is if you decide to insert more separators in the table or you don't want separators at all; but assuming that you can live with the two (and only those two), it's a good solution. [fn:2] BTW, you can turn on formula debugging from the Tbl menu or with C-c { and see what it is calculating: I used a slighty different table - added a few more rows with S-RET in column 1 and I also had a formula to calculate the squares in column 2, so the table looked like this: | a | b | |-------+---| | 1 | | | 2 | | | 3 | | | 4 | | | 5 | | | 6 | | |-------+---| | Total | | #+TBLFM: $LR2=vsum(@2..@-1)::$2 = pow($1, 2) Then pressing C-c C-c on the #+TBLFM line and saying yes a few times, you finally get this | a | b | |-------+----| | 1 | 1 | | 2 | 4 | | 3 | 9 | | 4 | 16 | | 5 | 25 | | 6 | 36 | |-------+----| | Total | 91 | #+TBLFM: $LR2 = vsum(@2..@-1)::$2 = pow($1, 2) with the following in the formula debugging buffer: ,---- | Substitution history of formula | Orig: vsum(@2..@-1) | $xyz-> vsum(@2..@-1) | @r$c-> vsum([1,4,9,16,25,36]) | $1-> vsum([1,4,9,16,25,36]) | Result: 91 | Format: NONE | Final: 91 `----