* 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 making flexible table formulas 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 making flexible table formulas 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-02-28 17:35 making flexible table formulas 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
* 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 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 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
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-02-28 17:35 making flexible table formulas Rustom Mody 2011-02-28 18:14 ` Luke Crook 2011-02-28 18:18 ` Nick Dokos 2011-03-01 9:08 ` Carsten Dominik -- strict thread matches above, loose matches on Subject: below -- 2011-03-01 3:19 Rustom Mody 2011-03-01 4:41 ` theo 2011-03-01 5:10 ` Nick Dokos
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).