emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* Problem with table sums
@ 2010-02-23 22:08 Sébastien Vauban
  2010-02-24  9:40 ` Carsten Dominik
  0 siblings, 1 reply; 6+ messages in thread
From: Sébastien Vauban @ 2010-02-23 22:08 UTC (permalink / raw)
  To: emacs-orgmode-mXXj517/zsQ

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 | 40000.00 |
   |   | Frais annexes     |     |  1280.00 |
   |   | Gestion du projet |     |  3200.00 |
   |   | Licence           |     |  8000.00 |
   |---+-------------------+-----+----------|
   |   | Total             |     | 52480.00 |
   | ^ |                   |     |    Total |
   #+TBLFM: @2$4=@2$3*400.00;%.2f::@3$4=@2$4*0.08*0.40;%.2f::@4$4=@2$4*0.08;%.2f::@5$4=@2$4*0.20;%.2f::@6$4=vsum(@-I..@-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 | 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)?

Best regards,
  Seb

-- 
Sébastien Vauban



_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: Problem with table sums
  2010-02-23 22:08 Problem with table sums Sébastien Vauban
@ 2010-02-24  9:40 ` Carsten Dominik
  2010-02-24 15:40   ` Dan Davison
  0 siblings, 1 reply; 6+ messages in thread
From: Carsten Dominik @ 2010-02-24  9:40 UTC (permalink / raw)
  To: Sébastien Vauban; +Cc: emacs-orgmode


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 | 40000.00 |
>   |   | Frais annexes     |     |  1280.00 |
>   |   | Gestion du projet |     |  3200.00 |
>   |   | Licence           |     |  8000.00 |
>   |---+-------------------+-----+----------|
>   |   | Total             |     | 52480.00 |
>   | ^ |                   |     |    Total |
>   #+TBLFM: @2$4=@2$3*400.00;%.2f::@3$4=@2$4*0.08*0.40;%. 
> 2f::@4$4=@2$4*0.08;%.2f::@5$4=@2$4*0.20;%.2f::@6$4=vsum(@-I..@-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 | 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.

- 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

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: Problem with table sums
  2010-02-24  9:40 ` Carsten Dominik
@ 2010-02-24 15:40   ` Dan Davison
  2010-02-25 11:11     ` Carsten Dominik
  0 siblings, 1 reply; 6+ messages in thread
From: Dan Davison @ 2010-02-24 15:40 UTC (permalink / raw)
  To: Carsten Dominik; +Cc: Sébastien Vauban, emacs-orgmode

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

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: Problem with table sums
  2010-02-24 15:40   ` Dan Davison
@ 2010-02-25 11:11     ` Carsten Dominik
  2010-03-01  0:37       ` Dan Davison
  0 siblings, 1 reply; 6+ messages in thread
From: Carsten Dominik @ 2010-02-25 11:11 UTC (permalink / raw)
  To: Dan Davison; +Cc: Sébastien Vauban, emacs-orgmode

:-)  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

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: Problem with table sums
  2010-02-25 11:11     ` Carsten Dominik
@ 2010-03-01  0:37       ` Dan Davison
  2010-03-01  7:49         ` Carsten Dominik
  0 siblings, 1 reply; 6+ messages in thread
From: Dan Davison @ 2010-03-01  0:37 UTC (permalink / raw)
  To: Carsten Dominik; +Cc: Sébastien Vauban, emacs-orgmode

Carsten Dominik <carsten.dominik@gmail.com> 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 <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
>
>
>
>
>
> _______________________________________________
> 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

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: Problem with table sums
  2010-03-01  0:37       ` Dan Davison
@ 2010-03-01  7:49         ` Carsten Dominik
  0 siblings, 0 replies; 6+ messages in thread
From: Carsten Dominik @ 2010-03-01  7:49 UTC (permalink / raw)
  To: Dan Davison; +Cc: Sébastien Vauban, emacs-orgmode

[-- Attachment #1: Type: text/plain, Size: 467 bytes --]


On Mar 1, 2010, at 1:37 AM, Dan Davison wrote:

> Carsten Dominik <carsten.dominik@gmail.com> 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:


[-- Attachment #2: pastedGraphic.tiff --]
[-- Type: image/tiff, Size: 19010 bytes --]

[-- Attachment #3: Type: text/plain, Size: 5407 bytes --]




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 <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
>>
>>
>>
>>
>>
>> _______________________________________________
>> 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




[-- Attachment #4: Type: text/plain, Size: 201 bytes --]

_______________________________________________
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

^ permalink raw reply	[flat|nested] 6+ messages in thread

end of thread, other threads:[~2010-03-01  8:18 UTC | newest]

Thread overview: 6+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2010-02-23 22:08 Problem with table sums Sébastien Vauban
2010-02-24  9:40 ` Carsten Dominik
2010-02-24 15:40   ` Dan Davison
2010-02-25 11:11     ` Carsten Dominik
2010-03-01  0:37       ` Dan Davison
2010-03-01  7:49         ` Carsten Dominik

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).