* making flexible table formulas
@ 2011-02-28 17:35 Rustom Mody
2011-02-28 18:14 ` Luke Crook
` (2 more replies)
0 siblings, 3 replies; 7+ messages in thread
From: Rustom Mody @ 2011-02-28 17:35 UTC (permalink / raw)
To: emacs-orgmode
[-- 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
^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: making flexible table formulas
2011-02-28 17:35 Rustom Mody
@ 2011-02-28 18:14 ` Luke Crook
2011-02-28 18:18 ` Nick Dokos
2011-03-01 9:08 ` Carsten Dominik
2 siblings, 0 replies; 7+ messages in thread
From: Luke Crook @ 2011-02-28 18:14 UTC (permalink / raw)
To: Rustom Mody; +Cc: emacs-orgmode
[-- 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
^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: making flexible table formulas
2011-02-28 17:35 Rustom Mody
2011-02-28 18:14 ` Luke Crook
@ 2011-02-28 18:18 ` Nick Dokos
2011-03-01 9:08 ` Carsten Dominik
2 siblings, 0 replies; 7+ messages in thread
From: Nick Dokos @ 2011-02-28 18:18 UTC (permalink / raw)
To: Rustom Mody; +Cc: nicholas.dokos, emacs-orgmode
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
^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: making flexible table formulas
@ 2011-03-01 3:19 Rustom Mody
2011-03-01 4:41 ` theo
2011-03-01 5:10 ` Nick Dokos
0 siblings, 2 replies; 7+ messages in thread
From: Rustom Mody @ 2011-03-01 3:19 UTC (permalink / raw)
To: emacs-orgmode
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...
^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: making flexible table formulas
2011-03-01 3:19 making flexible table formulas Rustom Mody
@ 2011-03-01 4:41 ` theo
2011-03-01 5:10 ` Nick Dokos
1 sibling, 0 replies; 7+ messages in thread
From: theo @ 2011-03-01 4:41 UTC (permalink / raw)
To: emacs-orgmode
-----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.
^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: making flexible table formulas
2011-03-01 3:19 making flexible table formulas Rustom Mody
2011-03-01 4:41 ` theo
@ 2011-03-01 5:10 ` Nick Dokos
1 sibling, 0 replies; 7+ messages in thread
From: Nick Dokos @ 2011-03-01 5:10 UTC (permalink / raw)
To: Rustom Mody; +Cc: nicholas.dokos, emacs-orgmode
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
`----
^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: making flexible table formulas
2011-02-28 17:35 Rustom Mody
2011-02-28 18:14 ` Luke Crook
2011-02-28 18:18 ` Nick Dokos
@ 2011-03-01 9:08 ` Carsten Dominik
2 siblings, 0 replies; 7+ messages in thread
From: Carsten Dominik @ 2011-03-01 9:08 UTC (permalink / raw)
To: Rustom Mody; +Cc: emacs-orgmode
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
^ permalink raw reply [flat|nested] 7+ messages in thread
end of thread, other threads:[~2011-03-01 10:24 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2011-03-01 3:19 making flexible table formulas Rustom Mody
2011-03-01 4:41 ` theo
2011-03-01 5:10 ` Nick Dokos
-- strict thread matches above, loose matches on Subject: below --
2011-02-28 17:35 Rustom Mody
2011-02-28 18:14 ` Luke Crook
2011-02-28 18:18 ` Nick Dokos
2011-03-01 9:08 ` 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).