From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?utf-8?Q?Oliver_Ve=C4=8Dern=C3=ADk?= Subject: Re: Spreadsheet calculations (24.3/8.0-pre) Date: Mon, 08 Apr 2013 16:14:01 +0200 Message-ID: <871ualhzyu.fsf@kerstf.org> References: <87li9lm3vj.fsf@kerstf.org> <87sj3rusnn.fsf@bzg.ath.cx> <878v5i62r0.fsf@kerstf.org> <807gkey8du.fsf@gmail.com> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from eggs.gnu.org ([208.118.235.92]:39960) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1UPEYM-00065l-36 for emacs-orgmode@gnu.org; Mon, 08 Apr 2013 12:04:22 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1UPEYB-0001B0-Ha for emacs-orgmode@gnu.org; Mon, 08 Apr 2013 12:04:17 -0400 Received: from plane.gmane.org ([80.91.229.3]:49949) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1UPEYB-0001An-Bz for emacs-orgmode@gnu.org; Mon, 08 Apr 2013 12:04:07 -0400 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1UPKA2-0003KP-2a for emacs-orgmode@gnu.org; Tue, 09 Apr 2013 00:03:34 +0200 Received: from 178.112.82.126.wireless.dyn.drei.com ([178.112.82.126]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 09 Apr 2013 00:03:34 +0200 Received: from ov by 178.112.82.126.wireless.dyn.drei.com with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 09 Apr 2013 00:03:34 +0200 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: emacs-orgmode@gnu.org Hi Ippei, > | Product | g | kJ/100g | kJ | kcal | > |-----------+------+---------+------+------| > | Bread | 50.6 | 1372 | 694 | 166 | > | Butter | 11.5 | 3054 | 351 | 84 | > | Marmalade | 19.7 | 926 | 182 | 44 | > |-----------+------+---------+------+------| > | | | | 1227 | 294 | > #+TBLFM: $3='(org-lookup-first $1 '(remote(nf,@I$1..@II$1)) > (remote(nf,@I$2..@II$2))) > #+TBLFM: $4='(* $2 (/ $3 $b));N%.0f > #+TBLFM: $5=$4/$j;%.0f > #+TBLFM: @>$4..$5=vsum(@I..II) > (Each TBLFM line has no linebreak.) thanks for your suggestion, but I didn't want an extra column. I played with `N' and `L' options and found following solution leaving them out entirely: #+TITLE: Nutrition Facts #+CONSTANTS: b=100.0 j=4.184 #+TBLNAME: nf | Product | kJ | kcal | |-------------+------+------| | Bread white | 1372 | 328 | | Butter | 3054 | 730 | | Marmalade | 926 | 221 | #+TBLFM: $3=$2/$j;%.0f | Product | g | kJ | kcal | |-------------+------+------+------| | Bread white | 50.6 | 694 | 166 | | Butter | 11.5 | 351 | 84 | | Marmalade | 19.7 | 182 | 43 | | nonexistent | | 0 | 0 | |-------------+------+------+------| | | | 1227 | 293 | #+TBLFM: $3='(* (string-to-number $2) (/ (string-to-number (org-lookup-last $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2)))) $b));%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) $1 has to be a string, because the lookup column can have more than one word. For the math I have to convert the strings to numbers. Maybe someone has an idea for a more elegant solution, but this works for me now. -- Best, Oliver