From mboxrd@z Thu Jan 1 00:00:00 1970 From: Martin Halder Subject: Re: table spreadsheet problem Date: Mon, 19 Mar 2012 17:25:08 +0100 Message-ID: References: <20120319153221.GN31713@x201> <17453.1332172608@alphaville> Mime-Version: 1.0 (Apple Message framework v1257) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([208.118.235.92]:55336) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1S9fOX-0005AC-Vw for emacs-orgmode@gnu.org; Mon, 19 Mar 2012 12:25:22 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1S9fOS-0000ps-J9 for emacs-orgmode@gnu.org; Mon, 19 Mar 2012 12:25:17 -0400 Received: from mail-we0-f169.google.com ([74.125.82.169]:60090) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1S9fOS-0000pc-AL for emacs-orgmode@gnu.org; Mon, 19 Mar 2012 12:25:12 -0400 Received: by werj55 with SMTP id j55so7769527wer.0 for ; Mon, 19 Mar 2012 09:25:10 -0700 (PDT) In-Reply-To: <17453.1332172608@alphaville> 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: nicholas.dokos@hp.com, Russell Adams Cc: emacs-orgmode@gnu.org Am 19.03.2012 um 16:56 schrieb Nick Dokos: > Russell Adams wrote: >=20 >> On Mon, Mar 19, 2012 at 04:01:42PM +0100, Martin Halder wrote: >>> Hi all, >>>=20 >>> question about table calculation. Found in the documentation that I = could refer to e.g. first line after second hline with @II+2. >>>=20 >>> Would like to calculate sum =3D vsum(@I..@II) and tax =3D sum * 0.08 >>>=20 >>> Am I doing sth wrong ? >>>=20 >>> Thanks for help, >>> Martin >>>=20 >>> | article | price | >>> |---------+--------| >>> | item1 | 100.00 | >>> | item2 | 200.00 | >>> |---------+--------| >>> | sum | | >>> | tax | | >>> |---------+--------| >>> | sum | 0 | >>> #+TBLFM: = @II+1$2=3Dvsum(@I..@II);f2N::@II+2$2=3D@II+1$2*0.08;f2N::@>$2=3Dvsum(@II..= @III);f2N >>>=20 >>>=20 >>=20 >> | article | price | >> |---------+--------| >> | item1 | 100.00 | >> | item2 | 200.00 | >> |---------+--------| >> | sum | 300.00 | >> | tax | 24.00 | >> |---------+--------| >> | sum | 324.00 | >> #+TBLFM: = @4$2=3Dvsum(@-I..@-II);%.2f::@5$2=3D@4$2*0.08;%.2f::@6$2=3Dvsum(@-I..@-II)= ;%.2f >>=20 >> I often use negative headline references for total lines in order to >> sum backward from the current cell. >>=20 >> So vsum(@-I..@-II);%.2f means sum the cells between the first = headline >> above the current cell to the second headline above the current cell, >> and then format the result as a decimal with two decimal places >> (%.2f). >>=20 >> Looking at your formula, I think you manually wrote the TBLFM >> line. One thing I've found is that the TBLFM line doesn't support >> relative references for the cell specification, only in the formula. >>=20 >> I used C-u C-c =3D to set mine. >>=20 >=20 > Nowadays, you can use (both on the LHS and the RHS of the formula) > symbolic references relative to the first, second, third ... row like > this: @<, @<<, @<< ... and relative to the last, penultimate, > antepenultimate (is there such a word?) row like this: @>, @>>, @>>> > ..., and similarly for columns, so you could write the formula, like > this: >=20 > #+TBLFM: = @>>>$>=3Dvsum(@-I..@-II);%.2f::@>>$>=3D@>>>$>*0.08;%.2f::@>$>=3Dvsum(@-I..= @-II);%.2f >=20 > This *does* require iterated evaluations, so C-u C-u C-c C-c is your > best bet, to make sure everything is updated properly. >=20 > Nick >=20 that is fantastic, many thanks for you help to you both. Martin >> Good luck! >>=20 >> ------------------------------------------------------------------ >> Russell Adams RLAdams@AdamsInfoServ.com >>=20 >> PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/ >>=20 >> Fingerprint: 1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3 >>=20 >=20