From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Brand Subject: Re: Difference between two rows Date: Sun, 1 Jun 2014 15:18:10 +0200 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:59499) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Wr5eP-00045b-NC for emacs-orgmode@gnu.org; Sun, 01 Jun 2014 09:18:14 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1Wr5eN-0000z4-38 for emacs-orgmode@gnu.org; Sun, 01 Jun 2014 09:18:13 -0400 Received: from mail-qa0-x236.google.com ([2607:f8b0:400d:c00::236]:48808) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Wr5eM-0000yk-UX for emacs-orgmode@gnu.org; Sun, 01 Jun 2014 09:18:11 -0400 Received: by mail-qa0-f54.google.com with SMTP id j15so1260405qaq.41 for ; Sun, 01 Jun 2014 06:18:10 -0700 (PDT) In-Reply-To: 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: Cecil Westerhof Cc: Org Mode Hi Cecil On Sun, Jun 1, 2014 at 2:24 PM, Cecil Westerhof wrote: > But what if I would want to know the > difference between the previous filled one, divided by the distance? > > For example: > |-------+------------| > | value | difference | > |-------+------------| > | 12 | | > | 15 | 3 | > | | | > | 83 | 34 | > | | | > | | | > | | | > | 87 | 1 | > | | | > | | | > | | | > | 85 | -0.5 | > | | | > |-------+------------| More as a demonstration of what Calc can do, divided up into some intermediate steps: |-------+---------------------------------------------------------| | value | difference | |-------+---------------------------------------------------------| | 12 | | | 15 | [15, 12] | | | | | 83 | [83, nan, 15, 12] | | | | | | | | | | | 87 | [87, nan, nan, nan, 83, nan, 15, 12] | | | | | | | | | | | 85 | [85, nan, nan, nan, 87, nan, nan, nan, 83, nan, 15, 12] | | | | |-------+---------------------------------------------------------| #+TBLFM: $2 = if(vlen(@-I$1..@0$1) == 1 || "$1" == "nan", string(""), rev(@-I$1..@0$1)); E |-------+----------------------------------------------------| | value | difference | |-------+----------------------------------------------------| | 12 | | | 15 | [0, 0] | | | | | 83 | [0, nan, 0, 0] | | | | | | | | | | | 87 | [0, nan, nan, nan, 0, nan, 0, 0] | | | | | | | | | | | 85 | [0, nan, nan, nan, 0, nan, nan, nan, 0, nan, 0, 0] | | | | |-------+----------------------------------------------------| #+TBLFM: $2 = if(vlen(@-I$1..@0$1) == 1 || "$1" == "nan", string(""), 0 * rev(@-I$1..@0$1)); E Distance to previous element: |-------+------------| | value | difference | |-------+------------| | 12 | | | 15 | 1 | | | | | 83 | 2 | | | | | | | | | | | 87 | 4 | | | | | | | | | | | 85 | 4 | | | | |-------+------------| #+TBLFM: $2 = if(vlen(@-I$1..@0$1) == 1 || "$1" == "nan", string(""), find(0 * rev(@-I$1..@0$1), 0, 2) - 1); E Previous element: |-------+------------| | value | difference | |-------+------------| | 12 | | | 15 | 12 | | | | | 83 | 15 | | | | | | | | | | | 87 | 83 | | | | | | | | | | | 85 | 87 | | | | |-------+------------| #+TBLFM: $2 = if(vlen(@-I$1..@0$1) == 1 || "$1" == "nan", string(""), subscr(rev(@-I$1..@0$1), find(0 * rev(@-I$1..@0$1), 0, 2)); E Combined: |-------+------------| | value | difference | |-------+------------| | 12 | | | 15 | 3 | | | | | 83 | 34 | | | | | | | | | | | 87 | 1 | | | | | | | | | | | 85 | -0.5 | | | | |-------+------------| #+TBLFM: $2 = if(vlen(@-I$1..@0$1) == 1 || "$1" == "nan", string(""), ($1 - subscr(rev(@-I$1..@0$1), find(0 * rev(@-I$1..@0$1), 0, 2))) / (find(0 * rev(@-I$1..@0$1), 0, 2) - 1)); E For many here on the list it would probably be easier and more interesting to write a TBLFM in Lisp. Michael