* Question on spreadsheet formula @ 2009-09-26 3:08 RC 2009-09-26 3:14 ` Benjamin Andresen 2009-09-26 3:34 ` Andreas Burtzlaff 0 siblings, 2 replies; 6+ messages in thread From: RC @ 2009-09-26 3:08 UTC (permalink / raw) To: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Question on spreadsheet formula 2009-09-26 3:08 Question on spreadsheet formula RC @ 2009-09-26 3:14 ` Benjamin Andresen 2009-09-26 3:34 ` Andreas Burtzlaff 1 sibling, 0 replies; 6+ messages in thread From: Benjamin Andresen @ 2009-09-26 3:14 UTC (permalink / raw) To: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Question on spreadsheet formula 2009-09-26 3:08 Question on spreadsheet formula RC 2009-09-26 3:14 ` Benjamin Andresen @ 2009-09-26 3:34 ` Andreas Burtzlaff 2009-09-27 19:35 ` RC 1 sibling, 1 reply; 6+ messages in thread From: Andreas Burtzlaff @ 2009-09-26 3:34 UTC (permalink / raw) To: RC; +Cc: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Question on spreadsheet formula 2009-09-26 3:34 ` Andreas Burtzlaff @ 2009-09-27 19:35 ` RC 2009-09-28 6:51 ` Carsten Dominik 0 siblings, 1 reply; 6+ messages in thread From: RC @ 2009-09-27 19:35 UTC (permalink / raw) To: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Re: Question on spreadsheet formula 2009-09-27 19:35 ` RC @ 2009-09-28 6:51 ` Carsten Dominik 2009-09-28 13:56 ` Bernt Hansen 0 siblings, 1 reply; 6+ messages in thread From: Carsten Dominik @ 2009-09-28 6:51 UTC (permalink / raw) To: RC; +Cc: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Question on spreadsheet formula 2009-09-28 6:51 ` Carsten Dominik @ 2009-09-28 13:56 ` Bernt Hansen 0 siblings, 0 replies; 6+ messages in thread From: Bernt Hansen @ 2009-09-28 13:56 UTC (permalink / raw) To: Carsten Dominik; +Cc: RC, emacs-orgmode 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
end of thread, other threads:[~2009-09-28 13:56 UTC | newest] Thread overview: 6+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2009-09-26 3:08 Question on spreadsheet formula RC 2009-09-26 3:14 ` Benjamin Andresen 2009-09-26 3:34 ` Andreas Burtzlaff 2009-09-27 19:35 ` RC 2009-09-28 6:51 ` Carsten Dominik 2009-09-28 13:56 ` Bernt Hansen
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).