* 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).