Re: [O] Spreadsheet calculations (24.3/8.0-pre)
Hi Oliver, Oliver Večerník o...@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
Re: [O] Spreadsheet calculations (24.3/8.0-pre)
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
Re: [O] Spreadsheet calculations (24.3/8.0-pre)
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.23201
(* 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
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.23201
Format: %.0f
Final: 694
#+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 o...@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.23201 | 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)
Re: [O] Spreadsheet calculations (24.3/8.0-pre)
Hi Oliver, Oliver Večerník o...@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
Re: [O] Spreadsheet calculations (24.3/8.0-pre)
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.23201 | 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
[O] Spreadsheet calculations (24.3/8.0-pre)
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
Re: [O] Spreadsheet calculations (24.3/8.0-pre)
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
