From mboxrd@z Thu Jan 1 00:00:00 1970 From: Carsten Dominik Subject: Re: Problem with table sums Date: Thu, 25 Feb 2010 12:11:36 +0100 Message-ID: <63628A50-3900-4D82-B9F1-B87096391D21@gmail.com> References: <87y6ijk3lm.fsf@mundaneum.com> <13C985E1-535E-42D0-9004-12E0618DEADA@gmail.com> <87ocje63qy.fsf@stats.ox.ac.uk> Mime-Version: 1.0 (Apple Message framework v936) Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1Nkbd9-0007s2-Lc for emacs-orgmode@gnu.org; Thu, 25 Feb 2010 06:11:43 -0500 Received: from [140.186.70.92] (port=60259 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Nkbd8-0007q8-AD for emacs-orgmode@gnu.org; Thu, 25 Feb 2010 06:11:42 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1Nkbd7-0006H8-8e for emacs-orgmode@gnu.org; Thu, 25 Feb 2010 06:11:42 -0500 Received: from mail-ew0-f222.google.com ([209.85.219.222]:34028) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1Nkbd6-0006Gx-WF for emacs-orgmode@gnu.org; Thu, 25 Feb 2010 06:11:41 -0500 Received: by ewy22 with SMTP id 22so1370021ewy.26 for ; Thu, 25 Feb 2010 03:11:39 -0800 (PST) In-Reply-To: <87ocje63qy.fsf@stats.ox.ac.uk> 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: Dan Davison Cc: =?ISO-8859-1?Q?S=E9bastien_Vauban?= , emacs-orgmode@gnu.org :-) I should have known. These days, babel is the answer to most =20 questions ... :-) - Carste On Feb 24, 2010, at 4:40 PM, Dan Davison wrote: > Carsten Dominik writes: > >> On Feb 23, 2010, at 11:08 PM, S=E9bastien Vauban wrote: > [...] >>> Though, I need to re-use some of the intermediate computations for >>> another >>> "summary table". Therefore, I add names to some cells: >>> >>> #+TBLNAME: etape1 >>> | | =C9tape 1 | p.j | EUR HTVA | >>> |---+-------------------+-----+-----------------| >>> | | Prestations | 100 | 40000.00 | >>> | ^ | | pj | Prestations | >>> | | Frais annexes | | 1280.00 | >>> | ^ | | | FraisAnnexes | >>> | | Gestion du projet | | 3200.00 | >>> | ^ | | | GestionDuProjet | >>> | | Licence | | 8000.00 | >>> | ^ | | | Licence | >>> |---+-------------------+-----+-----------------| >>> | | Total | | 40000.00 | >>> | ^ | | | Total | >>> #+TBLFM: @2$4=3D@2$3*400.00;%.2f::@4$4=3D@2$4*0.08*0.40;%. >>> 2f::@6$4=3D@2$4*0.08;%.2f::@8$4=3D@2$4*0.20;%.2f::@10$4=3Dvsum(@-I..@-= II); >>> %.2f >>> >>> Now, the total is wrong: it's the value of the first cell... Like if >>> the `^' >>> prefix was simply dropped... and total limited to the first real >>> figure. >>> >>> Any reason for this phenomenon? Or workaround (other than >>> describing every >>> cell to be summed)? >> >> Well, the reason is that the parser probably stops at the first text >> when summing, it tries to add "Prestations". >> >> I am afraid there is not good work-around for this. > > Hi Seb, > > Well, I was going to suggest using org-babel. After playing around =20 > for a > while, I ended up reading the org manual on table formulas and coming > back to a pure org solution. > > My simplest solution is almost straight out of the manual (which makes > me worry that I've missed the point of the question?): > > #+TBLFM:@10$4=3D'(apply '+ '(@-I..@-II));N > > But seeing as I've got them, I may as well post my org-babel =20 > solutions. > > Here's the first set of org-babel solutions, which are just like the > first solution, but use blocks to do the computation: > > #+TBLFM:@10$4=3D'(sbe my-sum-LANG (n (@-I..@-II)));N > > where LANG is whatever language you want to compute the sum in: > > #+function: my-sum-elisp(n) > #+begin_src emacs-lisp > (apply '+ n) > #+end_src > > #+function: my-sum-R(n) > #+begin_src R > sum(n) > #+end_src > > #+function: my-sum-python(n) > #+begin_src python > return sum(n) > #+end_src > > > The second set of org-babel solutions use org-babel to do the table > indexing. This was before I realised that I could use the @-I..@-II =20= > and > ;N syntax in conjunction with the org-babel sbe macro. So these ones > have to deal with separating the numeric entries from the character > strings. > > #+TBLFM:@10$4=3D'(sbe my-tab-sum-LANG);%.2f > > #+function: my-tab-sum-elisp > #+begin_src emacs-lisp :var tab=3Detape1[2:9,3] > (apply '+ (remq nil (mapcar (lambda (row) (if (numberp (car row)) =20 > (car row))) tab))) > #+end_src > > #+function: my-tab-sum-R > #+begin_src R :var tab=3Detape1[2:9,3] > sum(as.numeric(tab[[1]]), na.rm=3DTRUE) > #+end_src > > #+function: my-tab-sum-python > #+begin_src python :var tab=3Detape1[2:9,3] > flatten =3D lambda(lizt): sum(lizt, []) > return sum(filter(lambda x: isinstance(x, float), flatten(tab))) > #+end_src > > > Dan > > >> >> - Carsten >> >>> >>> Best regards, >>> Seb >>> >>> -- >>> S=E9bastien Vauban >>> >>> >>> >>> _______________________________________________ >>> Emacs-orgmode mailing list >>> Please use `Reply All' to send replies to the list. >>> Emacs-orgmode@gnu.org >>> http://lists.gnu.org/mailman/listinfo/emacs-orgmode >> >> - Carsten >> >> >> >> >> >> _______________________________________________ >> Emacs-orgmode mailing list >> Please use `Reply All' to send replies to the list. >> Emacs-orgmode@gnu.org >> http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten