* Spreadsheet calculations (24.3/8.0-pre) @ 2013-03-18 9:52 Oliver Večerník 2013-03-18 15:03 ` Christopher Allan Webber 2013-03-19 14:27 ` Bastien 0 siblings, 2 replies; 7+ messages in thread From: Oliver Večerník @ 2013-03-18 9:52 UTC (permalink / raw) To: emacs-orgmode Hi, I'm trying to do some simple calculations, but the results are plain wrong. I started the minimal example with `emacs -Q -l minimal.emacs org/minimal.org'. My Emacs is 24.3 with Org-mode version 8.0-pre (release_8.0-pre-116-g65cde8 @ /home/ov/p/org-mode/lisp/): #+TITLE: Nutrition Facts #+CONSTANTS: b=100 j=4.182 #+TBLNAME: nf | Product | kJ | kcal | |-----------+------+------| | Bread | 1372 | 328 | | Butter | 3054 | 730 | | Marmalade | 926 | 221 | #+TBLFM: $3=$2/$j;%.0f Here are some calculations per portion (plain wrong): | 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))) 100));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) Expected: | Product | g | kJ | kcal | |-----------+------+------+------| | Bread | 50.6 | 694 | 166 | | Butter | 11.5 | 351 | 84 | | Marmalade | 19.7 | 182 | 44 | |-----------+------+------+------| | | | 1227 | 294 | #+TBLFM: $4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) Using the constant b is also totally wrong: | Product | g | kJ | kcal | |-----------+------+----+------| | Bread | 50.6 | 0 | 0 | | Butter | 11.5 | 0 | 0 | | Marmalade | 19.7 | 0 | 0 | |-----------+------+----+------| | | | 0 | 0 | #+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) These results are achieved using `C-c C-c' on the first column of the format line. If I use `C-u C-c C-c' in the tabel I get different results *every* time. E.g. pressing `C-u C-c C-c' three times on the `B' of `Bread': | Product | g | kJ | kcal | |-----------+------+--------+-------| | Bread | 50.6 | 32606 | 7784 | | Butter | 11.5 | 59888 | 14297 | | Marmalade | 19.7 | 110192 | 26306 | |-----------+------+--------+-------| | | | 202686 | 48387 | #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) If I go to the end of the format line and press `C-c C-c' I get totally different results as at the beginning: | Product | g | 1064 | 254 | |-----------+------+------+------| | Bread | 50.6 | 1064 | 254 | | Butter | 11.5 | 1470 | 351 | | Marmalade | 19.7 | 2790 | 666 | |-----------+------+------+------| | | | 5324 | 1271 | #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) If I use `M-x org-table-recalculate-buffer-tables' even the headlines get screwed up. Am I doing something wrong or are there severe problems in the spreadsheet mode? Thanks in advance! -- Regards, Oliver ^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: Spreadsheet calculations (24.3/8.0-pre) 2013-03-18 9:52 Spreadsheet calculations (24.3/8.0-pre) Oliver Večerník @ 2013-03-18 15:03 ` Christopher Allan Webber 2013-03-19 14:27 ` Bastien 1 sibling, 0 replies; 7+ messages in thread From: Christopher Allan Webber @ 2013-03-18 15:03 UTC (permalink / raw) To: Oliver Večerník; +Cc: emacs-orgmode I've posted here before about it, but it looks like you're trying to do the same thing as I am; see https://gitorious.org/org-diet Here's an example of an org-diet file entry: | Food / Exercise | Calories | Quantity | Total | |----------------------------------------+----------+----------+-------| | thin & crispy flatbread | 16 | 2 | 32 | | tbsp neufchatel cheese spread | 35 | 1 | 35 | | tbsp apple butter | 30 | .5 | 15 | | tbsp jam | 50 | .5 | 25 | | Tea w/ agave & creamer | 40 | 1 | 40 | | cedarlane eggplant parmesan | 240 | 1 | 240 | | goldfish cracker | 2 | 20 | 40 | | bequet wrapped caramel | 48 | 1 | 48 | | Beverage w/ sugar in the raw & creamer | 40 | 1 | 40 | | pecan half | 10 | 3 | 30 | | orange | 62 | 1 | 62 | | presliced aged swiss cheese | 70 | 1 | 70 | | starbucks tall latte low fat milk | 109 | 1 | 109 | | 1 pkt sugar in the raw | 20 | 1 | 20 | | amy's cheese lasagna | 380 | 1 | 380 | | baby carrot | 4 | 3 | 12 | | cup low fat cottage cheese | 180 | .5 | 90 | | tofutti cutie | 130 | 1 | 130 | |----------------------------------------+----------+----------+-------| | Total | | | 1418 | #+TBLFM: $4=$2*$3;%.0f::$LR4=vsum(@2$4..@-I$4) That's not answering your question but might be useful given the type of things you appear to be entering :) Oliver Večerník writes: > Hi, > > I'm trying to do some simple calculations, but the results are plain > wrong. I started the minimal example with `emacs -Q -l minimal.emacs > org/minimal.org'. My Emacs is 24.3 with Org-mode version 8.0-pre > (release_8.0-pre-116-g65cde8 @ /home/ov/p/org-mode/lisp/): > > #+TITLE: Nutrition Facts > #+CONSTANTS: b=100 j=4.182 > > #+TBLNAME: nf > | Product | kJ | kcal | > |-----------+------+------| > | Bread | 1372 | 328 | > | Butter | 3054 | 730 | > | Marmalade | 926 | 221 | > #+TBLFM: $3=$2/$j;%.0f > > Here are some calculations per portion (plain wrong): > > | 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))) 100));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) > > Expected: > > | Product | g | kJ | kcal | > |-----------+------+------+------| > | Bread | 50.6 | 694 | 166 | > | Butter | 11.5 | 351 | 84 | > | Marmalade | 19.7 | 182 | 44 | > |-----------+------+------+------| > | | | 1227 | 294 | > #+TBLFM: $4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) > > Using the constant b is also totally wrong: > > | Product | g | kJ | kcal | > |-----------+------+----+------| > | Bread | 50.6 | 0 | 0 | > | Butter | 11.5 | 0 | 0 | > | Marmalade | 19.7 | 0 | 0 | > |-----------+------+----+------| > | | | 0 | 0 | > #+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) > > These results are achieved using `C-c C-c' on the first column of the > format line. If I use `C-u C-c C-c' in the tabel I get different > results *every* time. E.g. pressing `C-u C-c C-c' three times on the > `B' of `Bread': > > | Product | g | kJ | kcal | > |-----------+------+--------+-------| > | Bread | 50.6 | 32606 | 7784 | > | Butter | 11.5 | 59888 | 14297 | > | Marmalade | 19.7 | 110192 | 26306 | > |-----------+------+--------+-------| > | | | 202686 | 48387 | > #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) > > If I go to the end of the format line and press `C-c C-c' I get totally > different results as at the beginning: > > | Product | g | 1064 | 254 | > |-----------+------+------+------| > | Bread | 50.6 | 1064 | 254 | > | Butter | 11.5 | 1470 | 351 | > | Marmalade | 19.7 | 2790 | 666 | > |-----------+------+------+------| > | | | 5324 | 1271 | > #+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@>$3..$4=vsum(@I..II) > > If I use `M-x org-table-recalculate-buffer-tables' even the headlines > get screwed up. Am I doing something wrong or are there severe problems > in the spreadsheet mode? > > Thanks in advance! ^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: Spreadsheet calculations (24.3/8.0-pre) 2013-03-18 9:52 Spreadsheet calculations (24.3/8.0-pre) Oliver Večerník 2013-03-18 15:03 ` Christopher Allan Webber @ 2013-03-19 14:27 ` Bastien 2013-03-20 5:48 ` Oliver Večerník 1 sibling, 1 reply; 7+ messages in thread From: Bastien @ 2013-03-19 14:27 UTC (permalink / raw) To: Oliver Večerník; +Cc: emacs-orgmode Hi Oliver, Oliver Večerník <ov@vecernik.at> writes: > If I use `M-x org-table-recalculate-buffer-tables' even the headlines > get screwed up. Am I doing something wrong or are there severe problems > in the spreadsheet mode? #+CONSTANTS is meant to be used only once on the file, not per table. When used several times, `org-table-formula-constants-local' was defining the same constant several times, which is wrong. I fixed this. Let's take other problems one by one if you have time. Thanks, -- Bastien ^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: Spreadsheet calculations (24.3/8.0-pre) 2013-03-19 14:27 ` Bastien @ 2013-03-20 5:48 ` Oliver Večerník 2013-04-07 9:53 ` Ippei FURUHASHI 0 siblings, 1 reply; 7+ messages in thread From: Oliver Večerník @ 2013-03-20 5:48 UTC (permalink / raw) To: emacs-orgmode 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) -- Oliver ^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: Spreadsheet calculations (24.3/8.0-pre) 2013-03-20 5:48 ` Oliver Večerník @ 2013-04-07 9:53 ` Ippei FURUHASHI 2013-04-08 14:14 ` Oliver Večerník 0 siblings, 1 reply; 7+ messages in thread From: Ippei FURUHASHI @ 2013-04-07 9:53 UTC (permalink / raw) To: Oliver =?iso-2022-jp-2?B?VmUbJChEKy0bKEJlcm4bJChEKz8bKEJr?=; +Cc: emacs-orgmode [-- Attachment #1: Type: text/plain, Size: 1708 bytes --] 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 [-- Attachment #2: 2013-04-07-substitu-tion-history.txt --] [-- Type: text/plain, Size: 435 bytes --] 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 [-- Warning: decoded text below may be mangled, UTF-8 assumed --] [-- Attachment #3: Type: text/plain; charset=iso-2022-jp-2, Size: 2785 bytes --] #+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 <ov@vecernik.at> 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) ^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: Spreadsheet calculations (24.3/8.0-pre) 2013-04-07 9:53 ` Ippei FURUHASHI @ 2013-04-08 14:14 ` Oliver Večerník 2013-04-18 14:13 ` Bastien 0 siblings, 1 reply; 7+ messages in thread From: Oliver Večerník @ 2013-04-08 14:14 UTC (permalink / raw) To: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 7+ messages in thread
* Re: Spreadsheet calculations (24.3/8.0-pre) 2013-04-08 14:14 ` Oliver Večerník @ 2013-04-18 14:13 ` Bastien 0 siblings, 0 replies; 7+ messages in thread From: Bastien @ 2013-04-18 14:13 UTC (permalink / raw) To: Oliver Večerník; +Cc: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 7+ messages in thread
end of thread, other threads:[~2013-04-18 14:13 UTC | newest] Thread overview: 7+ messages (download: mbox.gz follow: Atom feed -- links below jump to the message on this page -- 2013-03-18 9:52 Spreadsheet calculations (24.3/8.0-pre) Oliver Večerník 2013-03-18 15:03 ` Christopher Allan Webber 2013-03-19 14:27 ` Bastien 2013-03-20 5:48 ` Oliver Večerník 2013-04-07 9:53 ` Ippei FURUHASHI 2013-04-08 14:14 ` Oliver Večerník 2013-04-18 14:13 ` Bastien
Code repositories for project(s) associated with this public inbox https://git.savannah.gnu.org/cgit/emacs/org-mode.git This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox; as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).