Spreadsheet bug?

Spreadsheet bug?

[meingbg]

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

Re: Spreadsheet bug?

[Andreas Burtzlaff]

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

Re: Spreadsheet bug?

[meingbg]

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

Re: Spreadsheet bug?

[Andreas Burtzlaff]

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

Re: Spreadsheet bug?

[Stephan Schmitt]

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

Re: Spreadsheet bug?

[meingbg]

> 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

Re: Spreadsheet bug?

[Carsten Dominik]

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