From mboxrd@z Thu Jan  1 00:00:00 1970
From: Bastien <bzg@gnu.org>
Subject: Re: Spreadsheet calculations (24.3/8.0-pre)
Date: Thu, 18 Apr 2013 16:13:11 +0200
Message-ID: <8761zk2ah4.fsf@bzg.ath.cx>
References: <87li9lm3vj.fsf@kerstf.org> <87sj3rusnn.fsf@bzg.ath.cx>
	<878v5i62r0.fsf@kerstf.org> <807gkey8du.fsf@gmail.com>
	<871ualhzyu.fsf@kerstf.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Return-path: <emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org>
Received: from eggs.gnu.org ([208.118.235.92]:45007)
	by lists.gnu.org with esmtp (Exim 4.71)
	(envelope-from <bastienguerry@gmail.com>) id 1USpaP-0002nN-Fs
	for emacs-orgmode@gnu.org; Thu, 18 Apr 2013 10:13:18 -0400
Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71)
	(envelope-from <bastienguerry@gmail.com>) id 1USpaO-0002H7-5n
	for emacs-orgmode@gnu.org; Thu, 18 Apr 2013 10:13:17 -0400
Received: from mail-wi0-x22a.google.com ([2a00:1450:400c:c05::22a]:53319)
	by eggs.gnu.org with esmtp (Exim 4.71)
	(envelope-from <bastienguerry@gmail.com>) id 1USpaN-0002Gf-Ua
	for emacs-orgmode@gnu.org; Thu, 18 Apr 2013 10:13:16 -0400
Received: by mail-wi0-f170.google.com with SMTP id l13so2627083wie.3
	for <emacs-orgmode@gnu.org>; Thu, 18 Apr 2013 07:13:15 -0700 (PDT)
In-Reply-To: <871ualhzyu.fsf@kerstf.org> ("Oliver =?utf-8?B?VmXEjWVybsOt?=
	=?utf-8?B?ayIncw==?= message of "Mon, 08 Apr 2013 16:14:01 +0200")
List-Id: "General discussions about Org-mode." <emacs-orgmode.gnu.org>
List-Unsubscribe: <https://lists.gnu.org/mailman/options/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/archive/html/emacs-orgmode>
List-Post: <mailto:emacs-orgmode@gnu.org>
List-Help: <mailto:emacs-orgmode-request@gnu.org?subject=help>
List-Subscribe: <https://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=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: Oliver =?utf-8?B?VmXEjWVybsOtaw==?= <ov@vecernik.at>
Cc: emacs-orgmode@gnu.org

Hi Oliver,

Oliver Večerník <ov@vecernik.at> writes:

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

I confirm there is no other elegant solution that either using an
additional column or using the internal conversion you used.  

-- 
 Bastien