Re: [Orgmode] Problem with table sums
On Mar 1, 2010, at 1:37 AM, Dan Davison wrote: Carsten Dominik writes: :-) I should have known. These days, babel is the answer to most questions ... :-) Well, that wasn't the conclusion I meant to be drawn -- I thought the pure org solution was best here! (at least for simple summation) Well, yes. But the pure Org-solution is not predictable. If you turn on formula debugging, you see that Org computes this: <> If the "name" of a field would read as "222GestionDuProjet" (which is not a valid name, yes), the effect of the N switch you added would convert this field into not 0, but 222. Furthermore, if the function had not been vsum, but vprod, the result would always be 0 because the "names" are turned into zeros. Using org-babel (or, if you like, emacs-lisp formulas) can try to throw out these names in the correct way. Really, what should happen is that Org should ignore "^" and "_" lines when interpreting the formulas - but that is unfortunately neither implemented, not easy to do (I think...) - Carsten Dan - Carste On Feb 24, 2010, at 4:40 PM, Dan Davison wrote: Carsten Dominik writes: On Feb 23, 2010, at 11:08 PM, Sébastien 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 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@- 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='(apply '+ '(@-...@-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='(sbe my-sum-LANG (n (@-...@-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 @-...@-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='(sbe my-tab-sum-LANG);%.2f #+function: my-tab-sum-elisp #+begin_src emacs-lisp :var tab=etape1[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=etape1[2:9,3] sum(as.numeric(tab[[1]]), na.rm=TRUE) #+end_src #+function: my-tab-sum-python #+begin_src python :var tab=etape1[2:9,3] flatten = lambda(lizt): sum(lizt, []) return sum(filter(lambda x: isinstance(x, float), flatten(tab))) #+end_src Dan - Carsten Best regards, Seb -- Sébastien 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 ___ 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 __
Re: [Orgmode] Problem with table sums
Carsten Dominik writes: > :-) I should have known. These days, babel is the answer to most > questions ... :-) Well, that wasn't the conclusion I meant to be drawn -- I thought the pure org solution was best here! (at least for simple summation) Dan > > - Carste > > On Feb 24, 2010, at 4:40 PM, Dan Davison wrote: > >> Carsten Dominik writes: >> >>> On Feb 23, 2010, at 11:08 PM, Sébastien 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 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-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='(apply '+ '(@-...@-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='(sbe my-sum-LANG (n (@-...@-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 @-...@-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='(sbe my-tab-sum-LANG);%.2f >> >> #+function: my-tab-sum-elisp >> #+begin_src emacs-lisp :var tab=etape1[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=etape1[2:9,3] >> sum(as.numeric(tab[[1]]), na.rm=TRUE) >> #+end_src >> >> #+function: my-tab-sum-python >> #+begin_src python :var tab=etape1[2:9,3] >> flatten = lambda(lizt): sum(lizt, []) >> return sum(filter(lambda x: isinstance(x, float), flatten(tab))) >> #+end_src >> >> >> Dan >> >> >>> >>> - Carsten >>> Best regards, Seb -- Sébastien 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 > > > > > > ___ > 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 ___ 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
Re: [Orgmode] Problem with table sums
:-) I should have known. These days, babel is the answer to most 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ébastien 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 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-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='(apply '+ '(@-...@-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='(sbe my-sum-LANG (n (@-...@-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 @-...@-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='(sbe my-tab-sum-LANG);%.2f #+function: my-tab-sum-elisp #+begin_src emacs-lisp :var tab=etape1[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=etape1[2:9,3] sum(as.numeric(tab[[1]]), na.rm=TRUE) #+end_src #+function: my-tab-sum-python #+begin_src python :var tab=etape1[2:9,3] flatten = lambda(lizt): sum(lizt, []) return sum(filter(lambda x: isinstance(x, float), flatten(tab))) #+end_src Dan - Carsten Best regards, Seb -- Sébastien 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 ___ 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
Re: [Orgmode] Problem with table sums
Carsten Dominik writes: > On Feb 23, 2010, at 11:08 PM, Sébastien 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 >> | | Étape 1 | p.j | EUR HTVA| >> |---+---+-+-| >> | | Prestations | 100 | 4.00| >> | ^ | | pj | Prestations | >> | | Frais annexes | | 1280.00 | >> | ^ | | | FraisAnnexes| >> | | Gestion du projet | | 3200.00 | >> | ^ | | | GestionDuProjet | >> | | Licence | | 8000.00 | >> | ^ | | | Licence | >> |---+---+-+-| >> | | Total | | 4.00| >> | ^ | | | Total | >> #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. >> 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-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='(apply '+ '(@-...@-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='(sbe my-sum-LANG (n (@-...@-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 @-...@-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='(sbe my-tab-sum-LANG);%.2f #+function: my-tab-sum-elisp #+begin_src emacs-lisp :var tab=etape1[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=etape1[2:9,3] sum(as.numeric(tab[[1]]), na.rm=TRUE) #+end_src #+function: my-tab-sum-python #+begin_src python :var tab=etape1[2:9,3] flatten = lambda(lizt): sum(lizt, []) return sum(filter(lambda x: isinstance(x, float), flatten(tab))) #+end_src Dan > > - Carsten > >> >> Best regards, >> Seb >> >> -- >> Sébastien 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 ___ 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
Re: [Orgmode] Problem with table sums
On Feb 23, 2010, at 11:08 PM, Sébastien Vauban wrote: Hello, I have such a table whose formatting is forced by someone more powerful than me (my boss): #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA | |---+---+-+--| | | Prestations | 100 | 4.00 | | | Frais annexes | | 1280.00 | | | Gestion du projet | | 3200.00 | | | Licence | | 8000.00 | |---+---+-+--| | | Total | | 52480.00 | | ^ | | |Total | #+TBLFM: @2...@2$3*400.00;%.2f::@3...@2$4*0.08*0.40;%. 2f::@4...@2$4*0.08;%.2f::@5...@2$4*0.20;%.2f::@6$4=vsum(@-...@-ii);%. 2f It works as expected. Though, I need to re-use some of the intermediate computations for another "summary table". Therefore, I add names to some cells: #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-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. - Carsten Best regards, Seb -- Sébastien 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
[Orgmode] Problem with table sums
Hello, I have such a table whose formatting is forced by someone more powerful than me (my boss): #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA | |---+---+-+--| | | Prestations | 100 | 4.00 | | | Frais annexes | | 1280.00 | | | Gestion du projet | | 3200.00 | | | Licence | | 8000.00 | |---+---+-+--| | | Total | | 52480.00 | | ^ | | |Total | #+TBLFM: @2...@2$3*400.00;%.2f::@3...@2$4*0.08*0.40;%.2f::@4...@2$4*0.08;%.2f::@5...@2$4*0.20;%.2f::@6$4=vsum(@-...@-ii);%.2f It works as expected. Though, I need to re-use some of the intermediate computations for another "summary table". Therefore, I add names to some cells: #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%.2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-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)? Best regards, Seb -- Sébastien 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
