Re: Spreadsheet calculations (24.3/8.0-pre)

[Ippei FURUHASHI <top.tuna+orgmode@gmail.com>]

[-- 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)