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