emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* 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 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).