From mboxrd@z Thu Jan 1 00:00:00 1970 From: Ippei FURUHASHI Subject: Re: Spreadsheet calculations (24.3/8.0-pre) Date: Sun, 07 Apr 2013 18:53:17 +0900 Message-ID: <807gkey8du.fsf@gmail.com> References: <87li9lm3vj.fsf@kerstf.org> <87sj3rusnn.fsf@bzg.ath.cx> <878v5i62r0.fsf@kerstf.org> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Return-path: Received: from eggs.gnu.org ([208.118.235.92]:54009) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1UOmI8-0000rQ-78 for emacs-orgmode@gnu.org; Sun, 07 Apr 2013 05:53:47 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1UOmI1-0002jP-7n for emacs-orgmode@gnu.org; Sun, 07 Apr 2013 05:53:40 -0400 Received: from mail-pa0-f49.google.com ([209.85.220.49]:65187) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1UOmI0-0002j5-Ts for emacs-orgmode@gnu.org; Sun, 07 Apr 2013 05:53:33 -0400 Received: by mail-pa0-f49.google.com with SMTP id kp14so2764703pab.36 for ; Sun, 07 Apr 2013 02:53:31 -0700 (PDT) In-Reply-To: <878v5i62r0.fsf@kerstf.org> ("Oliver =?iso-2022-jp-2?B?VmU=?= =?iso-2022-jp-2?B?GyQoRCstGyhCZXJuGyQoRCs/GyhCayIncw==?= message of "Wed, 20 Mar 2013 06:48:03 +0100") 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: Oliver =?iso-2022-jp-2?B?VmUbJChEKy0bKEJlcm4bJChEKz8bKEJr?= Cc: emacs-orgmode@gnu.org --=-=-= Hi Oliver, If you can add a column, how about this first-aid? | 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.) >From here, it is no more than a first-aid. It's just a tracing log, and it has no conclusion for this issue. I hope this would help you get the new direction, if you need. You got the results calculated wrongly, that is: > | Product | g | kJ | kcal | > |-----------+------+------+------| > | Bread | 50.6 | 694 | 166 | > | Butter | 11.5 | 158 | 38 | > | Marmalade | 19.7 | 270 | 65 | > |-----------+------+------+------| > | | | 1122 | 269 | > #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100.0));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) This seems to me that: #+BEGIN_SRC emacs-lisp (* 50.6 (/ 1372 100.0)) ; => 694.2320000000001 (* 11.5 (/ 1372 100.0)) ; => 157.78 (* 19.7 (/ 1372 100.0)) ; => 270.284 #+END_SRC You didn't want 1372 for all the cases, if I understood your calculation correctly. Where did it(=1372) come from? Turning on the formula debugging with =C-c {=, and then Hitting =C-c *= in the field of @2$3 (whose value is 694) said: #+BEGIN_EXAMPLE --=-=-= Content-Disposition: inline; filename=2013-04-07-substitu-tion-history.txt Substitution history of formula Orig: '(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) $b));N%.0f $xyz-> '(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100.0)) @r$c-> '(* $2 (/ (org-lookup-first $1 '(0 0 0) '(1372 3054 926)) 100.0)) $1-> '(* 50.6 (/ (org-lookup-first 0 '(0 0 0) '(1372 3054 926)) 100.0)) Result: 694.2320000000001 Format: %.0f Final: 694 --=-=-= Content-Type: text/plain; charset=iso-2022-jp-2 #+END EXAMPLE I have a question for it. In the line starting with "$1->", why was 0 substituted into $1? Unfortunatelly, I had to suspend this tracing, because I need much more time to edebug `org-table-eval-formula' with my capablitliy. If you are interested in, feel free to go further. HTH, IP Oliver Ve$(D+-(Bern$(D+?(Bk writes: > Hi Bastien, > >> #+CONSTANTS is meant to be used only once on the file, not per table. > > that's how I understood it. > >> When used several times, `org-table-formula-constants-local' was >> defining the same constant several times, which is wrong. I fixed >> this. > > I can confirm this is working now. > >> Let's take other problems one by one if you have time. > > Sure. Let's start with following tables and Org-mode version 8.0-pre > (release_8.0-pre-144-g855dcf @ /home/ov/p/org-mode/lisp/): > > #+TITLE: Nutrition Facts > #+CONSTANTS: b=100.0 j=4.182 > > #+TBLNAME: nf > | Product | kJ | kcal | > |-----------+------+------| > | Bread | 1372 | 328 | > | Butter | 3054 | 730 | > | Marmalade | 926 | 221 | > #+TBLFM: $3=$2/$j;%.0f > > | Product | g | kJ | kcal | > |-----------+------+------+------| > | Bread | 50.6 | 658 | 157 | > | Butter | 11.5 | 150 | 36 | > | Marmalade | 19.7 | 256 | 61 | > |-----------+------+------+------| > | | | 1064 | 254 | > #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) $b));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) > > I'm reapplying formulas by pressing `C-c C-c' on the hash mark of the > format line. The expected results are: > > | Product | g | kJ | kcal | > |-----------+------+------+------| > | Bread | 50.6 | 694 | 166 | > | Butter | 11.5 | 351 | 84 | > | Marmalade | 19.7 | 182 | 44 | > |-----------+------+------+------| > | | | 1227 | 294 | > > Let's take this apart: > > #+BEGIN_SRC emacs-lisp > (values (* 50.6 (/ 1372 100.0)) > (* 11.5 (/ 3054 100.0)) > (* 19.7 (/ 926 100.0))) > #+END_SRC > #+RESULTS: > | 694.2320000000001 | 351.21 | 182.422 | > > I was bitten myself by setting `b=100', which is an integer and led to > the wrong result. But Org-mode still calculates as if `b' were an > integer. But even replacing `$b' with `100.0' still gives wrong results > (second and third line): > > | Product | g | kJ | kcal | > |-----------+------+------+------| > | Bread | 50.6 | 694 | 166 | > | Butter | 11.5 | 158 | 38 | > | Marmalade | 19.7 | 270 | 65 | > |-----------+------+------+------| > | | | 1122 | 269 | > #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100.0));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) --=-=-=--