* Field coordinates and moving averages
@ 2011-11-15 15:21 Benjamin Beckwith
2011-11-15 15:52 ` Nick Dokos
0 siblings, 1 reply; 3+ messages in thread
From: Benjamin Beckwith @ 2011-11-15 15:21 UTC (permalink / raw)
To: emacs-orgmode
I'm trying to create a table of periodic data and calculate a moving
average in an adjacent column. I was trying to use field coordinates,
but they do not work how I would like them to work.
Here is what I have for a 5-day moving average:
| Daily Data | Moving Average |
|------------+----------------|
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 14 | |
| 16 | |
#+TBLFM: $2=if(@# >=7, vmean(@-5$1..@0$1),string(""));
I expeced the if(@# >=7..) to protect the sub-clauses from executing
unless the predicate is true. However, that is not the case. When I
try to execute this formula, I receive the error: "Row descriptor -5
used in line ### leads outside table".
I can see how this is true if the ranges are checked for every entry.
Is there a better way for me to write this column forumla? Is this a
limitation in the field coordinates?
Thanks,
Ben
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: Field coordinates and moving averages
2011-11-15 15:21 Field coordinates and moving averages Benjamin Beckwith
@ 2011-11-15 15:52 ` Nick Dokos
2011-11-15 19:16 ` Michael Brand
0 siblings, 1 reply; 3+ messages in thread
From: Nick Dokos @ 2011-11-15 15:52 UTC (permalink / raw)
To: Benjamin Beckwith; +Cc: nicholas.dokos, emacs-orgmode
Benjamin Beckwith <bnbeckwith@gmail.com> wrote:
>
> I'm trying to create a table of periodic data and calculate a moving
> average in an adjacent column. I was trying to use field coordinates,
> but they do not work how I would like them to work.
>
> Here is what I have for a 5-day moving average:
>
> | Daily Data | Moving Average |
> |------------+----------------|
> | 10 | |
> | 11 | |
> | 12 | |
> | 13 | |
> | 14 | |
> | 14 | |
> | 16 | |
> #+TBLFM: $2=if(@# >=7, vmean(@-5$1..@0$1),string(""));
>
> I expeced the if(@# >=7..) to protect the sub-clauses from executing
> unless the predicate is true. However, that is not the case. When I
> try to execute this formula, I receive the error: "Row descriptor -5
> used in line ### leads outside table".
>
> I can see how this is true if the ranges are checked for every entry.
> Is there a better way for me to write this column forumla? Is this a
> limitation in the field coordinates?
>
Try a range on the LHS:
#+TBLFM: @7$2..@>$2=vmean(@-5$1..@0$1)
FWIW, I avoid calc conditionals because I don't really understand how
they work. E.g. you are assuming that it evals the condition and iff
true, it proceeds to eval one or the other of the clauses, but the lisp
error shows that that's not how it works. I prefer to deal with such
problems by writing a lisp function instead.
Nick
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: Field coordinates and moving averages
2011-11-15 15:52 ` Nick Dokos
@ 2011-11-15 19:16 ` Michael Brand
0 siblings, 0 replies; 3+ messages in thread
From: Michael Brand @ 2011-11-15 19:16 UTC (permalink / raw)
To: Benjamin Beckwith; +Cc: nicholas.dokos, Org Mode
Hi Ben
On Tue, Nov 15, 2011 at 16:21, Benjamin Beckwith <bnbeckwith@gmail.com> wrote:
> #+TBLFM: $2=if(@# >=7, vmean(@-5$1..@0$1),string(""));
The above does not work because the range expression "@-5$1..@0$1" is
an Org construct and has to be evaluated for every affected row
already in Org, before it is given to Calc. It has therefore no other
choice than failing together with "$2 =" (out of range), the
expression "@# >= 7" is evaluated only in Calc later. This is one of
the reasons for which I added "field coordinates in formulas" (@# and
$#) in 2010-03.
There are at least two solutions with a Calc formula for this use
case:
1) range formula
The clean solution, already given by Nick:
On Tue, Nov 15, 2011 at 16:52, Nick Dokos <nicholas.dokos@hp.com> wrote:
> Try a range on the LHS:
> #+TBLFM: @7$2..@>$2=vmean(@-5$1..@0$1)
2) Calc subvec with field coordinates in formulas
This was the only solution before Carsten introduced range formulas in
2011-03 and might be interesting for understanding purposes:
| Daily Data | Moving Average |
|------------+----------------|
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 14 | 12.333 |
| 16 | 13.333 |
#+TBLFM: $2 = if(@# >= 7, vmean(subvec(@I$1..@>$1, @# - 6, @#)) +.0,
string("")); f-3
See also "Dynamic variation of ranges" in "field coordinates in
formulas (@# and $#)" here:
http://orgmode.org/worg/org-hacks.html
reachable about one or two pages down with the current section
numbering here:
http://orgmode.org/worg/org-hacks.html#sec-1-3-5
For such a two-dimensional moving range, Calc subsrc/subvec is still
the simpler solution to get the triangle than several range formulas
would be.
If you change the input field 16 to 20 you see that "+.0" is required
in the formula to get 14.000 instead of 14 for the result. See "Which
float format shows the fraction part also when the latter is zero?"
here:
http://orgmode.org/worg/org-faq.html#table-float-fraction
Michael
^ permalink raw reply [flat|nested] 3+ messages in thread
end of thread, other threads:[~2011-11-15 19:17 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2011-11-15 15:21 Field coordinates and moving averages Benjamin Beckwith
2011-11-15 15:52 ` Nick Dokos
2011-11-15 19:16 ` Michael Brand
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).