making flexible table formulas

making flexible table formulas

[Rustom Mody]

[-- Attachment #1.1: Type: text/plain, Size: 787 bytes --]

When using orgmode for hacking on data in a table (org a la spreadsheet)  I
have this situation
Say I am concentrating on column 2 and I want the bottom cell to be the sum
of the above cells
For a 7 row table with 8th row having the total I get

  #+TBLFM: @8$2=vsum(@1$2..@7$2)

But now I have a problem: If say I add a row to the table then the next time
I recompute the formula(s) the ninth row is not affected and the 8th row
which is now data gets overwritten with a computation.

So basically I want the @1 and @7 which are hardcoded above to be replaced
by something to the effect: "everything above..." and the @8$2 should be
something to the tune of "bottom of $2"

I guess this may not be a reasonable request -- but with org you never know
:-) so asking if there is some way.

[-- Attachment #1.2: Type: text/html, Size: 846 bytes --]

[-- Attachment #2: 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

Re: making flexible table formulas

[Luke Crook]

[-- Attachment #1.1: Type: text/plain, Size: 1082 bytes --]

On Mon, Feb 28, 2011 at 9:35 AM, Rustom Mody <rustompmody@gmail.com> wrote:

> When using orgmode for hacking on data in a table (org a la spreadsheet)  I
> have this situation
> Say I am concentrating on column 2 and I want the bottom cell to be the sum
> of the above cells
> For a 7 row table with 8th row having the total I get
>
>   #+TBLFM: @8$2=vsum(@1$2..@7$2)
>
> But now I have a problem: If say I add a row to the table then the next
> time I recompute the formula(s) the ninth row is not affected and the 8th
> row which is now data gets overwritten with a computation.
>
> So basically I want the @1 and @7 which are hardcoded above to be replaced
> by something to the effect: "everything above..." and the @8$2 should be
> something to the tune of "bottom of $2"
>

Place the rows you want to sum between horizontal separator lines (see
http://orgmode.org/manual/Built_002din-table-editor.html#Built_002din-table-editor
)

Then you can do the following:

  #+TBLFM: @8$2=vsum(@I..@II)

Which means, sum the columns between the first and the second separators.

-Luke

[-- Attachment #1.2: Type: text/html, Size: 1563 bytes --]

[-- Attachment #2: 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

Re: making flexible table formulas

[Nick Dokos]

Rustom Mody <rustompmody@gmail.com> wrote:

> When using orgmode for hacking on data in a table (org a la spreadsheet)  I have this situation
> Say I am concentrating on column 2 and I want the bottom cell to be the sum of the above cells
> For a 7 row table with 8th row having the total I get
> 
>   #+TBLFM: @8$2=vsum(@1$2..@7$2)
> 
> But now I have a problem: If say I add a row to the table then the next time I recompute the formula
> (s) the ninth row is not affected and the 8th row which is now data gets overwritten with a
> computation.
> 
> So basically I want the @1 and @7 which are hardcoded above to be replaced by something to the
> effect: "everything above..." and the @8$2 should be something to the tune of "bottom of $2"
> 
> I guess this may not be a reasonable request -- but with org you never know :-) so asking if there
> is some way.
> 
> 

Of course it's reasonable - and of course, org implements it :-)

#+TBLFM: $LR2=vsum(@1..@-1)

In words: column 2 of the last row is the sum of all the rows
(implicitly in column 2) from row 1 to the row above the last one.

See section 3.5.1, "Tables>The spreadsheet>References" for more details.

Nick

Re: making flexible table formulas

[Rustom Mody]

Nick Dokos wrote:

> Of course it's reasonable - and of course, org implements it
> #+TBLFM: $LR2=vsum(@1..@-1)

Thanks Nick I can use that. But I dont understand it.  What's the -1?
The manual says -- relative to 'current' column.
What determines 'current?' There must be some obvious POV which I am missing...

Re: making flexible table formulas

[theo]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/03/2011 04:19, Rustom Mody wrote:
> Nick Dokos wrote:
> 
>> Of course it's reasonable - and of course, org implements it
>> #+TBLFM: $LR2=vsum(@1..@-1)
> 
> Thanks Nick I can use that. But I dont understand it.  What's the -1?
> The manual says -- relative to 'current' column.
> What determines 'current?' There must be some obvious POV which I am missing...

@X = row X, absolute.
@-X = X rows before, relative.

For instance :

I*1 |A--|
   1| 1 |
   2| 2 |
   3| 3 |
I*2 |A--|
   4| 6 |
I*3 |A--|
#+TBLFM: $1=vsum(@1..@-1)

Here the 4th row is the sum. So @-1refers to the 3rd row.

If we add a new row before, the sum still works.

I*1 |A---|
   1|  1 |
   2|  2 |
   3|  3 |
   4| 42 |
I*2 |A---|
   5| 48 |
I*3 |A---|
#+TBLFM: $1=vsum(@1..@-1)

@-1 now refers to the 4 row.
- -- 
freely yours,
theo
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNbHjoAAoJECkgngj8k9TvPggIAKXvaaz4HfzPOiU2RAajBqYq
/qFYvk5Pf3e1QJme8qiiY0JWiW6isS+YI7bg9Hg6XLe5F3qxm20zz3Z008jWyZuN
TBhsr6ox0B3Hf3OnienQrhUvm8v1bIWiEhJFd/qBG0mp8dY5zeuJro6bLWrZxKhn
zVO0dw7rh3xQoYIOzRIoP1wnykg2xw3st4GH4JqdSusLhAwy/AfkvjgXBf3qbZ/p
FU8ksalYgQUb9S+GGpFdzIyPqJ+d7m3JbAQHbZ0Wpak0oCR306BGIcBuqMqzAZCd
5ukuHJr+Ug4Mlt+OjreAJajeMUVAYTSCU46nupuEJysK4egKEz0LLcNPzoXr7BU=
=o6Qk
-----END PGP SIGNATURE-----

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: making flexible table formulas

[Nick Dokos]

Rustom Mody <rustompmody@gmail.com> wrote:

> Nick Dokos wrote:
> 
> > Of course it's reasonable - and of course, org implements it
> > #+TBLFM: $LR2=vsum(@1..@-1)
> 
> Thanks Nick I can use that. But I dont understand it.  What's the -1?
> The manual says -- relative to 'current' column.
> What determines 'current?' There must be some obvious POV which I am missing...
> 

The formula calculates $LR2: the cell in the last row, column 2.
That's what determines 'current'. Since there is no column spec
on the right-hand side, column 2 is implied. IOW, the formula
is equivalent to this:

#+TBLFM: $LR2=vsum(@1$2..@-1$2)

The vsum on the RHS ranges then from row 1, column 2 (@1$2)
to the row above the last one, still on column 2 (@-1$2).

BTW, if you have a header you should start at row 2 (separator
lines don't count):

|     A |  B |
|-------+----|
|     1 |  1 |
|     2 |  4 |
|     3 |  9 |
| Total | 14 |
#+TBLFM: $LR2=vsum(@2..@-1)

or use the alternative syntax that Luke Crook suggested [fn:1]

#+TBLFM: $LR2=vsum(@I..@-1)

which goes from the first separator (@I) to the penultimate row
(@-1) - and remember that separator lines are ignored in the
calculation.

HTH, [fn:2]
Nick

Footnotes:
[fn:1] Luke suggested the following formula:

#+TBLFM: @8$2=vsum(@I..@II)

with the assumption that there will be two separator lines,
one after the header and one just before the last row,
in effect demarcating the useful part of the table:


|     A |  B |
|-------+----|
|     1 |  1 |
|     2 |  4 |
|     3 |  9 |
|-------+----|
| Total | 14 |
#+TBLFM: $LR2=vsum(@I..@II)

[Luke's LHS fixes the row to 8 which is not correct - I corrected
that in the above formula.]

The only problem with this is if you decide to insert more separators in
the table or you don't want separators at all; but assuming that you can
live with the two (and only those two), it's a good solution.

[fn:2] BTW, you can turn on formula debugging from the Tbl menu or with C-c {
and see what it is calculating: I used a slighty different table - added a few 
more rows with S-RET in column 1 and I also had a formula to calculate
the squares in column 2, so the table looked like this:

|     a | b |
|-------+---|
|     1 |   |
|     2 |   |
|     3 |   |
|     4 |   |
|     5 |   |
|     6 |   |
|-------+---|
| Total |   |

#+TBLFM: $LR2=vsum(@2..@-1)::$2 = pow($1, 2)

Then pressing C-c C-c on the #+TBLFM line and saying yes a few times,
you finally get this 

|     a |  b |
|-------+----|
|     1 |  1 |
|     2 |  4 |
|     3 |  9 |
|     4 | 16 |
|     5 | 25 |
|     6 | 36 |
|-------+----|
| Total | 91 |

#+TBLFM: $LR2 = vsum(@2..@-1)::$2 = pow($1, 2)

with the following in the formula debugging buffer:

,----
| Substitution history of formula
| Orig:   vsum(@2..@-1)
| $xyz->  vsum(@2..@-1)
| @r$c->  vsum([1,4,9,16,25,36])
| $1->    vsum([1,4,9,16,25,36])
| Result: 91
| Format: NONE
| Final:  91
`----

Re: making flexible table formulas

[Carsten Dominik]

On 28.2.2011, at 18:35, Rustom Mody wrote:

> When using orgmode for hacking on data in a table (org a la spreadsheet)  I have this situation
> Say I am concentrating on column 2 and I want the bottom cell to be the sum of the above cells
> For a 7 row table with 8th row having the total I get
> 
>   #+TBLFM: @8$2=vsum(@1$2..@7$2)
> 
> But now I have a problem: If say I add a row to the table then the next time I recompute the formula(s) the ninth row is not affected and the 8th row which is now data gets overwritten with a computation.

Hi Rustom,

in addition to the excellent advice you have already gotten in this thread,
I would like to add the following piece of information (if only to make
the thread a more complete online reference):

Apparently you have been inserting new rows with normal editing commands like
RET or `C-o'.  If you use Org's special commands to insert new rows, for example
M-S-<down> on the last row, the row references in the formula will be adjusted
to

 @9$2=vsum(@1$2..@7$2)

So you see that the @8 has become a @9.  However, the @7 has not been
adjusted because Org has no way of telling what you meant.  So even if
you write your formula with @8$2=, you should use relative references to
refer to the fields when summing:

@8$2==vsum(@1$2..@-1$2)


Anyhow, the methods proposed by Nick and Luke are much better in this case.

Hope this helps

- Carsten


> 
> So basically I want the @1 and @7 which are hardcoded above to be replaced by something to the effect: "everything above..." and the @8$2 should be something to the tune of "bottom of $2"
> 
> I guess this may not be a reasonable request -- but with org you never know :-) so asking if there is some way.
> _______________________________________________
> 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