From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Brand Subject: Re: Field coordinates and moving averages Date: Tue, 15 Nov 2011 20:16:33 +0100 Message-ID: References: <878vnhpfw5.fsf@bnbeckwith.com> <26090.1321372379@alphaville.dokosmarshall.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Return-path: Received: from eggs.gnu.org ([140.186.70.92]:59896) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1RQOVe-0003GR-Tk for emacs-orgmode@gnu.org; Tue, 15 Nov 2011 14:17:35 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1RQOVa-0001Mt-D4 for emacs-orgmode@gnu.org; Tue, 15 Nov 2011 14:17:30 -0500 Received: from mail-wy0-f169.google.com ([74.125.82.169]:53020) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1RQOVa-0001L4-5k for emacs-orgmode@gnu.org; Tue, 15 Nov 2011 14:17:26 -0500 Received: by wyi11 with SMTP id 11so1493429wyi.0 for ; Tue, 15 Nov 2011 11:17:24 -0800 (PST) In-Reply-To: <26090.1321372379@alphaville.dokosmarshall.org> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Benjamin Beckwith Cc: nicholas.dokos@hp.com, Org Mode Hi Ben On Tue, Nov 15, 2011 at 16:21, Benjamin Beckwith 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 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