Re: Problem with table sums

[Carsten Dominik <carsten.dominik@gmail.com>]

:-)  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 <carsten.dominik@gmail.com> 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 | 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=@2$3*400.00;%.2f::@4$4=@2$4*0.08*0.40;%.
>>> 2f::@6$4=@2$4*0.08;%.2f::@8$4=@2$4*0.20;%.2f::@10$4=vsum(@-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='(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='(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='(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