From mboxrd@z Thu Jan 1 00:00:00 1970 From: Dan Davison Subject: Re: Problem with table sums Date: Wed, 24 Feb 2010 10:40:53 -0500 Message-ID: <87ocje63qy.fsf@stats.ox.ac.uk> References: <87y6ijk3lm.fsf@mundaneum.com> <13C985E1-535E-42D0-9004-12E0618DEADA@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1NkJMD-0007DE-C0 for emacs-orgmode@gnu.org; Wed, 24 Feb 2010 10:41:01 -0500 Received: from [140.186.70.92] (port=38566 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1NkJMC-0007Cb-ER for emacs-orgmode@gnu.org; Wed, 24 Feb 2010 10:41:00 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1NkJMB-0001ns-6t for emacs-orgmode@gnu.org; Wed, 24 Feb 2010 10:41:00 -0500 Received: from markov.stats.ox.ac.uk ([163.1.210.1]:54729) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1NkJMA-0001nd-RV for emacs-orgmode@gnu.org; Wed, 24 Feb 2010 10:40:59 -0500 In-Reply-To: <13C985E1-535E-42D0-9004-12E0618DEADA@gmail.com> (Carsten Dominik's message of "Wed, 24 Feb 2010 10:40:01 +0100") 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: Carsten Dominik Cc: =?utf-8?Q?S=C3=A9bastien?= Vauban , emacs-orgmode@gnu.org Carsten Dominik writes: > On Feb 23, 2010, at 11:08 PM, S=C3=A9bastien 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 >> | | =C3=89tape 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 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 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 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)) (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=C3=A9bastien 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