Question on spreadsheet formula

Question on spreadsheet formula

[RC]

Hi,
On applying the column formula on the table shown below:
  |-----+------+------+----------|
  | No. |    P |    E | B        |
  |-----+------+------+----------|
  |     |      |      | 0        |
  |   1 | 5000 | 2000 |          |
  |   2 | 7000 | 1000 |          |
  |   3 | 5000 | 1000 |          |
#+TBLFM: $4=@-1$4+$2-$3

I get:
  |-----+------+------+-----------|
  | No. |    P |    E | B         |
  |-----+------+------+-----------|
  |     |      |      | B         |
  |   1 | 5000 | 2000 | B + 3000  |
  |   2 | 7000 | 1000 | B + 9000  |
  |   3 | 5000 | 1000 | B + 13000 |
#+TBLFM: $4=@-1$4+$2-$3

When what I would like is:
  |-----+------+------+-------|
  | No. |    P |    E | B     |
  |-----+------+------+-------|
  |     |      |      | 0     |
  |   1 | 5000 | 1000 | 3000  |
  |   2 | 5000 | 1000 | 9000  |
  |   3 | 5000 | 1000 | 13000 |
#+TBLFM: $4=@-1$4+$2-$3

Is there a way I can restrict application of the column formula to below the
horizontal line.
Thanks,
RC

Re: Question on spreadsheet formula

[Benjamin Andresen]

Hey RC,

If you set the value of D2 to 0 what you want will be achieved.

The table would look like this:

|-----+------+------+-------|
| No. |    P |    E |     B |
|-----+------+------+-------|
|     |      |      |     0 |
|   1 | 5000 | 2000 |  3000 |
|   2 | 7000 | 1000 |  9000 |
|   3 | 5000 | 1000 | 13000 |
#+TBLFM: $4=@-1$4+$2-$3::@2$4=0
                         ^^^^
                         That's the interesting part


> Is there a way I can restrict application of the column formula to below the
> horizontal line.

No idea.

> Thanks,
> RC

br,
benny

Re: Question on spreadsheet formula

[Andreas Burtzlaff]

Hi,

you could rewrite the formula using vertical sums:

|-----+------+------+-------|
| No. |    P |    E |     B |
|-----+------+------+-------|
|   1 | 5000 | 2000 |  3000 |
|   2 | 7000 | 1000 |  9000 |
|   3 | 5000 | 1000 | 13000 |
#+TBLFM: $4=vsum(@-I$2..@0$2)-vsum(@-I$3..@0$3)

HTH

Andreas


On Sat, 26 Sep 2009 03:08:11 +0000 (UTC)
RC <recif@yahoo.com> wrote:

> Hi,
> On applying the column formula on the table shown below:
>   |-----+------+------+----------|
>   | No. |    P |    E | B        |
>   |-----+------+------+----------|
>   |     |      |      | 0        |
>   |   1 | 5000 | 2000 |          |
>   |   2 | 7000 | 1000 |          |
>   |   3 | 5000 | 1000 |          |
> #+TBLFM: $4=@-1$4+$2-$3
> 
> I get:
>   |-----+------+------+-----------|
>   | No. |    P |    E | B         |
>   |-----+------+------+-----------|
>   |     |      |      | B         |
>   |   1 | 5000 | 2000 | B + 3000  |
>   |   2 | 7000 | 1000 | B + 9000  |
>   |   3 | 5000 | 1000 | B + 13000 |
> #+TBLFM: $4=@-1$4+$2-$3
> 
> When what I would like is:
>   |-----+------+------+-------|
>   | No. |    P |    E | B     |
>   |-----+------+------+-------|
>   |     |      |      | 0     |
>   |   1 | 5000 | 1000 | 3000  |
>   |   2 | 5000 | 1000 | 9000  |
>   |   3 | 5000 | 1000 | 13000 |
> #+TBLFM: $4=@-1$4+$2-$3
> 
> Is there a way I can restrict application of the column formula to below the
> horizontal line.
> Thanks,
> RC
> 
> 
> 
> 
> _______________________________________________
> 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: Question on spreadsheet formula

[RC]

Thank you benny and Andreas for your solutions to my question.

I did notice a couple of things I did not expect and would appreciate if someone
could tell me what I am missing.

In the following table, if I change the value in @2$4, I have to run C-c C-c
twice for the change to be propagated. Turning on formula debugging, the first
time the first formula gets executed starting only from @3$4 until the end of
the table, then the second substitution formula is executed for cell @2#4.  This
happens even if cells @2$2 and @2$3 have values in them. Does the presence of
the substitution formula later in the #+TBLFM line, cause execution of the first
formula to start only from the succeeding row?
|-----+------+------+-------|
| No. |    P |    E |     B |
|-----+------+------+-------|
|     |      |      |   300 |
|   1 | 5000 | 2000 |  3200 |
|   2 | 7000 | 1000 |  9200 |
|   3 | 5000 | 1000 | 13200 |
#+TBLFM: $4=@-1$4+$2-$3::@2$4=300


For the table above, or the following, having a ! in the first column of the
first row (which I thought was a way to label columns) causes the calculation
formulas not to be executed.
|---+-----+------+------+-------|
| ! | No. |    P |    E |     B |
|---+-----+------+------+-------|
|   |   1 | 5000 | 2000 |  3000 |
|   |   2 | 7000 | 1000 |  9000 |
|   |   3 | 5000 | 1000 | 13000 |
|   |   4 | 5000 | 2000 | 16000 |
#+TBLFM: $5=vsum(@-I$3..@0$3)-vsum(@-I$4..@0$4)

Thanks,
RC

Re: Re: Question on spreadsheet formula

[Carsten Dominik]

Hi RC,

On Sep 27, 2009, at 8:35 PM, RC wrote:

> Thank you benny and Andreas for your solutions to my question.
>
> I did notice a couple of things I did not expect and would  
> appreciate if someone
> could tell me what I am missing.
>
> In the following table, if I change the value in @2$4, I have to run  
> C-c C-c
> twice for the change to be propagated. Turning on formula debugging,  
> the first
> time the first formula gets executed starting only from @3$4 until  
> the end of
> the table, then the second substitution formula is executed for cell  
> @2#4.  This
> happens even if cells @2$2 and @2$3 have values in them. Does the  
> presence of
> the substitution formula later in the #+TBLFM line, cause execution  
> of the first
> formula to start only from the succeeding row?
> |-----+------+------+-------|
> | No. |    P |    E |     B |
> |-----+------+------+-------|
> |     |      |      |   300 |
> |   1 | 5000 | 2000 |  3200 |
> |   2 | 7000 | 1000 |  9200 |
> |   3 | 5000 | 1000 | 13200 |
> #+TBLFM: $4=@-1$4+$2-$3::@2$4=300


Filed formulas are applied after column formulas, because
often these reference fields that have been changed by column formulas.
You have here a case where this assumption doe not apply.

Note that there is a command org-table-iterate that recomputes
the table until it is stable.   `C-u C-u C-c C-u' does call this
command when the cursor is in table.

So far this did not work when the cursor is on the TBLFM line, but
I just changed that.  C-u C-u C-c C-c from the TBLFM line now also
iterates.

>
>
> For the table above, or the following, having a ! in the first  
> column of the
> first row (which I thought was a way to label columns) causes the  
> calculation
> formulas not to be executed.
> |---+-----+------+------+-------|
> | ! | No. |    P |    E |     B |
> |---+-----+------+------+-------|
> |   |   1 | 5000 | 2000 |  3000 |
> |   |   2 | 7000 | 1000 |  9000 |
> |   |   3 | 5000 | 1000 | 13000 |
> |   |   4 | 5000 | 2000 | 16000 |
> #+TBLFM: $5=vsum(@-I$3..@0$3)-vsum(@-I$4..@0$4)

If you are using a special first column, only lines with # or * in the
first column will be touched by column formulas.  This is in the docs,
right where you can read about the "!" rows as well.  Here is the
relevant passage:

 > Important: please note that for these special tables, recalculating
 > the table with C-u C-c * will only affect rows that are marked  
‘#’
 > or ‘*’, and fields that have a formula assigned to the field  
itself.
 > The column formulas are not applied in rows with empty first field.

HTH

- Carsten

>
> Thanks,
> RC
>
>
>
> _______________________________________________
> 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: Question on spreadsheet formula

[Bernt Hansen]

Carsten Dominik <carsten.dominik@gmail.com> writes:

> Note that there is a command org-table-iterate that recomputes
> the table until it is stable.   `C-u C-u C-c C-u' does call this

  The key sequence should be       C-u C-u C-c C-c  as below.

> command when the cursor is in table.
>
> So far this did not work when the cursor is on the TBLFM line, but
> I just changed that.  C-u C-u C-c C-c from the TBLFM line now also
> iterates.

-Bernt