[-- Attachment #1.1: Type: text/plain, Size: 623 bytes --] I have a table like this one: | a | b | c | |---+----+---| | a | 2 | 2 | | b | 3 | 7 | | c | -3 | 4 | | d | 5 | 9 | #+TBLFM: $3=$2+@-1::@2$3=$2 Now, I want the value in column c in every row to be the sum of column b so far. I try to achieve this by writing a column formula for column $3 and a cell formula for cell @2$3. Note that cell @3$3 is incorrect whereas all the other are correct. I have tried different formulas and values, and it seems like the incorrect cell is the sum of three cells instead of two as intended. @2$2 wasn't supposed to be part of the sum. Am I doing something incorrectly here? //M.G. [-- Attachment #1.2: Type: text/html, Size: 670 bytes --] [-- Attachment #2: Type: text/plain, Size: 204 bytes --] _______________________________________________ Emacs-orgmode mailing list Remember: use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
On Thu, 23 Jul 2009 02:29:30 +0200 meingbg <meingbg@gmail.com> wrote: > | a | b | c | > |---+----+---| > | a | 2 | 2 | > | b | 3 | 7 | > | c | -3 | 4 | > | d | 5 | 9 | > #+TBLFM: $3=$2+@-1::@2$3=$2 A good solution is to use $3=vsum(@-I$-1..$-1) as proposed in this thread: http://thread.gmane.org/gmane.emacs.orgmode/1940/focus=1941 Nevertheless: If @2$3 is manually set to 2 in the table, then the formulas produce the expected outcome. So, the problem seems to be the order of execution. If column c is cleared and the formulas are executed, @2$3 is read to be 0 for the summation and is set to 2 only afterwards. Swapping the order in the TBLFM line doesn't help. Is the order something like field formulas after column formulas? Any chance to influence that? Andy
> A good solution is to use > $3=vsum(@-I$-1..$-1) > as proposed in this thread: > http://thread.gmane.org/gmane.emacs.orgmode/1940/focus=1941 That works for me, thanks! > > Nevertheless: > > If @2$3 is manually set to 2 in the table, then the formulas produce > the expected outcome. So, the problem seems to be the order of > execution. If column c is cleared and the formulas are executed, @2$3 > is read to be 0 for the summation and is set to 2 only afterwards. If that was the case, wouldn't @3$3 sum up to 3+0=3? Now it shows 7.
On Thu, 23 Jul 2009 12:17:03 +0200
meingbg <meingbg@gmail.com> wrote:
> > Nevertheless:
> >
> > If @2$3 is manually set to 2 in the table, then the formulas produce
> > the expected outcome. So, the problem seems to be the order of
> > execution. If column c is cleared and the formulas are executed, @2$3
> > is read to be 0 for the summation and is set to 2 only afterwards.
>
> If that was the case, wouldn't @3$3 sum up to 3+0=3? Now it shows 7.
I suspect @2$3 was 4 when you evaluated the formulas, hence the 7.
If you clear column c completely, what is the output of the evaluation then?
I get:
| a | b | c |
|---+----+---|
| a | 2 | 2 |
| b | 3 | 3 |
| c | -3 | 0 |
| d | 5 | 5 |
Andy
Andreas Burtzlaff wrote: > On Thu, 23 Jul 2009 02:29:30 +0200 meingbg <meingbg@gmail.com> wrote: >> > | a | b | c | >> > |---+----+---| >> > | a | 2 | 2 | >> > | b | 3 | 7 | >> > | c | -3 | 4 | >> > | d | 5 | 9 | >> > #+TBLFM: $3=$2+@-1::@2$3=$2 > > If @2$3 is manually set to 2 in the table, then the formulas produce > the expected outcome. So, the problem seems to be the order of > execution. If column c is cleared and the formulas are executed, @2$3 > is read to be 0 for the summation and is set to 2 only afterwards. > Swapping the order in the TBLFM line doesn't help. > > Is the order something like field formulas after column formulas? > Any chance to influence that? Evaluating the field formulas before the column formulas is in general a bad idea. Results of the field formulas will always be overwritten by the column formulas (if there is a conflict). Here you need to evaluate the spreadsheet twice to yield the intended result (I get a 5 in field @2$3). From info:org:Updating the table: > `C-u C-u C-c *' > `C-u C-u C-c C-c' > Iterate the table by recomputing it until no further changes occur. > This may be necessary if some computed fields use the value of > other fields that are computed later in the calculation sequence. hth, Stephan
> I suspect @2$3 was 4 when you evaluated the formulas, hence the 7. > If you clear column c completely, what is the output of the evaluation then? The same. And so now I can't reproduce my original problem... that's good, I guess. > A good solution is to use > $3=vsum(@-I$-1..$-1) That works. Thanks! Here's another thing I'm trying: | a | b | c | | | 7 | 210 | |---+----+-----| | a | 2 | 30 | | b | 3 | 5 | | c | -3 | 2 | | d | 5 | 7 | #+TBLFM: @2$2=vsum(@3$2..@6$2)::@2$3=vsum(@3$3..$LR3) I want row 2 to contain sums for what's below. Is there a way to make the LR-reference work here? //meingbg
On Jul 24, 2009, at 11:20 AM, Stephan Schmitt wrote: > Andreas Burtzlaff wrote: >> On Thu, 23 Jul 2009 02:29:30 +0200 meingbg <meingbg@gmail.com> wrote: >>>> | a | b | c | >>>> |---+----+---| >>>> | a | 2 | 2 | >>>> | b | 3 | 7 | >>>> | c | -3 | 4 | >>>> | d | 5 | 9 | >>>> #+TBLFM: $3=$2+@-1::@2$3=$2 >> > >> If @2$3 is manually set to 2 in the table, then the formulas produce >> the expected outcome. So, the problem seems to be the order of >> execution. If column c is cleared and the formulas are executed, @2$3 >> is read to be 0 for the summation and is set to 2 only afterwards. >> Swapping the order in the TBLFM line doesn't help. >> >> Is the order something like field formulas after column formulas? >> Any chance to influence that? > > Evaluating the field formulas before the column formulas is in > general a bad > idea. Results of the field formulas will always be overwritten by > the column > formulas (if there is a conflict). When recomputing a table, Org evaluates field formulas first. Then it does the column formulas, but never overwrites a field that was filled with a field formula. As mentioned elsewhere in this thread > > Here you need to evaluate the spreadsheet twice to yield the > intended result (I > get a 5 in field @2$3). Indeed, this is what is needed here. - Carsten > From info:org:Updating the table: > >> `C-u C-u C-c *' >> `C-u C-u C-c C-c' >> Iterate the table by recomputing it until no further changes >> occur. >> This may be necessary if some computed fields use the value of >> other fields that are computed later in the calculation sequence. > > hth, > Stephan > > > _______________________________________________ > Emacs-orgmode mailing list > Remember: use `Reply All' to send replies to the list. > Emacs-orgmode@gnu.org > http://lists.gnu.org/mailman/listinfo/emacs-orgmode