From: Ippei FURUHASHI <top.tuna+orgmode@gmail.com>
To: Oliver =?iso-2022-jp-2?B?VmUbJChEKy0bKEJlcm4bJChEKz8bKEJr?=
<ov@vecernik.at>
Cc: emacs-orgmode@gnu.org
Subject: Re: Spreadsheet calculations (24.3/8.0-pre)
Date: Sun, 07 Apr 2013 18:53:17 +0900 [thread overview]
Message-ID: <807gkey8du.fsf@gmail.com> (raw)
In-Reply-To: <878v5i62r0.fsf@kerstf.org> ("Oliver =?iso-2022-jp-2?B?VmU=GyQoRCstGyhCZXJuGyQoRCs/GyhCayIncw==?= message of "Wed, 20 Mar 2013 06:48:03 +0100")
[-- 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)
next prev parent reply other threads:[~2013-04-07 9:53 UTC|newest]
Thread overview: 7+ messages / expand[flat|nested] mbox.gz Atom feed top
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 [this message]
2013-04-08 14:14 ` Oliver Večerník
2013-04-18 14:13 ` Bastien
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=807gkey8du.fsf@gmail.com \
--to=top.tuna+orgmode@gmail.com \
--cc=emacs-orgmode@gnu.org \
--cc=ov@vecernik.at \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
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).