Re: [O] Calculate differences of remote table numbers
On Monday, 8 Oct 2018 at 17:19, Karl Voit wrote: > Unfortunately, I have to admit that spreadsheets in Org are not > something I can recommend to somebody who needs "something that just > works" (like Excel-switcher). :-( Maybe not but the combination of "It's all text", Emacs calc, and LaTeX export is unbeatable for me. I've not used any other spreadsheet in years! -- Eric S Fraga via Emacs 27.0.50, Org release_9.1.13-894-gf79545
Re: [O] Calculate differences of remote table numbers
* Michael Brand wrote:
> Hi Karl
Hi Michael,
> On Mon, Oct 1, 2018 at 5:02 PM Karl Voit wrote:
>
>> I'd like to calculate the differences between rows of numbers of a
>> different table.
>
> For this kind of shifting row or column indexes I use Calc vector
> subscript. In your case:
>
> #+NAME: my-table
>| Numbers |
>|-|
>| 1 |
>| 5 |
>| 8 |
>| 12 |
>| 15 |
>
>| Line | Difference |
>|--+|
>|1 ||
>|2 | 4 |
>|3 | 3 |
>|4 | 4 |
>|5 | 3 |
> #+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
> @I$1..@II$1), @# - 1) - subscr(remote(my-table, @I$1..@II$1), @# - 2))
>
> or, avoiding @# completely in the formula for $2:
>
> #+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
> @I$1..@II$1), $1) - subscr(remote(my-table, @I$1..@II$1), $1 - 1))
>
> See also a similar example of subscr in the subsection "Dynamic
> variation of ranges" here:
> https://orgmode.org/worg/org-hacks.html#field-coordinates-in-formulas
Thanks for your post - it is working as expected! \o/
Also thanks for the URL - I added it to my knowledge base.
Unfortunately, I have to admit that spreadsheets in Org are not
something I can recommend to somebody who needs "something that just
works" (like Excel-switcher). :-(
However, when a table is finally working as expected, I really do
appreciate having this table (and its derived data) within my notes
and not locked away in a separate file.
--
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
> get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/
Re: [O] Calculate differences of remote table numbers
Hi Karl
On Mon, Oct 1, 2018 at 5:02 PM Karl Voit wrote:
> I'd like to calculate the differences between rows of numbers of a
> different table.
For this kind of shifting row or column indexes I use Calc vector
subscript. In your case:
#+NAME: my-table
| Numbers |
|-|
| 1 |
| 5 |
| 8 |
| 12 |
| 15 |
| Line | Difference |
|--+|
|1 ||
|2 | 4 |
|3 | 3 |
|4 | 4 |
|5 | 3 |
#+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
@I$1..@II$1), @# - 1) - subscr(remote(my-table, @I$1..@II$1), @# - 2))
or, avoiding @# completely in the formula for $2:
#+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
@I$1..@II$1), $1) - subscr(remote(my-table, @I$1..@II$1), $1 - 1))
See also a similar example of subscr in the subsection "Dynamic
variation of ranges" here:
https://orgmode.org/worg/org-hacks.html#field-coordinates-in-formulas
Michael
Re: [O] Calculate differences of remote table numbers
Karl Voit writes:
>>
>> @# is the row number, so to refer to that row, you need @@#. Something like
>> this,
>> although I didn't test to see if everything gets parsed correctly:
>>
>> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#,
>> 0, remote(my-table,@@#-1$1))::$4=if(2 == @#, 0,
>> (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
>
> This is a huge step forward:
>
> #+NAME: my-table
> | Numbers |
>
> |-|
> | 1 |
> | 5 |
> | 8 |
> | 12 |
> | 15 |
>
> | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
> |--+-+-+-|
> |1 | 0 | 0 | 0 |
> |2 | 5 | 1 | 4 |
> |3 | 8 | 5 | 3 |
> |4 | 12 | 8 | 4 |
> |5 | 15 | 12 | 3 |
>
>
> | Line | actual Reference1 | actual Reference2 | Difference |
> |--+---+---+|
> |1 | 0 | 0 | 0 |
> |2 | 5 | 5 | 0 |
> |3 | 8 | 8 | 0 |
> |4 |12 |12 | 0 |
> |5 |15 |15 | 0 |
>
> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0,
> remote(my-table,@@#-1$1))::$4=if(2 == @#, 0,
> (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
>
> Somehow, the "-1" in "@@#-1$1" does not have any effect: "@@#$1" and
> "@@#-1$" gives the same results.
>
> Do you have any idea why?
Yes, that's what I was afraid of when I said "...if everything is parsed
correctly".
AFAICT, you cannot use expressions (I tried parentheses and curly braces as
well: the
formula debugger complains that it expected a number.
One workaround is to add a column to the original table where you *can*
calculate
using row/column expressions and then use each column where appropriate in the
remote references. Something like this:
--8<---cut here---start->8---
#+NAME: my-table
| Numbers | shifted |
|-+-|
| 1 | 0 |
| 5 | 1 |
| 8 | 5 |
| 12 | 8 |
| 15 | 12 |
#+TBLFM: @2$2..@>$2 = if(2==@#, 0, @-1$1)
| Line | exp. Reference1 | exp. Reference2 | exp. Difference |
|--+-+-+-|
|1 | 0 | 0 | 0 |
|2 | 5 | 1 | 4 |
|3 | 8 | 5 | 3 |
|4 | 12 | 8 | 4 |
|5 | 15 | 12 | 3 |
| Line | actual Reference1 | actual Reference2 | Difference |
|--+---+---+|
|1 | 0 | 0 | 0 |
|2 | 5 | 1 | 4 |
|3 | 8 | 5 | 3 |
|4 |12 | 8 | 4 |
|5 |15 |12 | 3 |
#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0,
remote(my-table,@@#$2))::$4=$2-$3
--8<---cut here---end--->8---
But somebody (perhaps Michael Brand, if he is around) might come up
with better ideas.
>
>> Turning on formula debugging with `C-c {' helps.
>
> Woha! I didn't know that! And I also didn't know =C-c }= for
> displaying column/row numbers :-O *This* is going to be handy for me
> ;-)
Both Emacs and Org Mode are unending sources of new discovery and
delight!-)
--
Nick
"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler
Re: [O] Calculate differences of remote table numbers
Hi Nick,
* Nick Dokos wrote:
> Karl Voit writes:
>
>> I'd like to calculate the differences between rows of numbers of a
>> different table.
>>
>> Here is a minimal example showing the issue:
>>
>> #+NAME: my-table
>> | Numbers |
>>
>> |-|
>> | 1 |
>> | 5 |
>> | 8 |
>> | 12 |
>> | 15 |
>>
>> This is what I expected: "exp. Reference1" and "exp. Reference2" is here only
>> to demonstrate that the assumed references are wrong in the third table.
>> "exp.
>> Difference" is the column I want to get in the first place. I don't want to
>> see
>> Reference 1 or 2 at all in my solution (since I need a couple of those
>> difference-columns).
>>
>> | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
>> |--+-+-+-|
>> |1 | 0 | 0 | 0 |
>> |2 | 5 | 1 | 4 |
>> |3 | 8 | 5 | 3 |
>> |4 | 12 | 8 | 4 |
>> |5 | 15 | 12 | 3 |
>>
>> This is what I get instead:
>>
>> | Line | actual Reference1 | actual Reference2 | Difference |
>> |--+---+---+|
>> |1 | 0 | 0 | 0 |
>> |2 | 6 | 1 | 5 |
>> |3 |12 | 1 | 11 |
>> |4 |20 | 1 | 19 |
>> |5 |30 | 1 | 29 |
>
> @# is the row number, so to refer to that row, you need @@#. Something like
> this,
> although I didn't test to see if everything gets parsed correctly:
>
> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0,
> remote(my-table,@@#-1$1))::$4=if(2 == @#, 0,
> (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
This is a huge step forward:
#+NAME: my-table
| Numbers |
|-|
| 1 |
| 5 |
| 8 |
| 12 |
| 15 |
| Line | exp. Reference1 | exp. Reference2 | exp. Difference |
|--+-+-+-|
|1 | 0 | 0 | 0 |
|2 | 5 | 1 | 4 |
|3 | 8 | 5 | 3 |
|4 | 12 | 8 | 4 |
|5 | 15 | 12 | 3 |
| Line | actual Reference1 | actual Reference2 | Difference |
|--+---+---+|
|1 | 0 | 0 | 0 |
|2 | 5 | 5 | 0 |
|3 | 8 | 8 | 0 |
|4 |12 |12 | 0 |
|5 |15 |15 | 0 |
#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0,
remote(my-table,@@#-1$1))::$4=if(2 == @#, 0,
(remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
Somehow, the "-1" in "@@#-1$1" does not have any effect: "@@#$1" and
"@@#-1$" gives the same results.
Do you have any idea why?
> Turning on formula debugging with `C-c {' helps.
Woha! I didn't know that! And I also didn't know =C-c }= for
displaying column/row numbers :-O *This* is going to be handy for me
;-)
--
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
> get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/
Re: [O] Calculate differences of remote table numbers
Karl Voit writes:
> Hi!
>
> I'd like to calculate the differences between rows of numbers of a
> different table.
>
> Here is a minimal example showing the issue:
>
> #+NAME: my-table
> | Numbers |
>
> |-|
> | 1 |
> | 5 |
> | 8 |
> | 12 |
> | 15 |
>
> This is what I expected: "exp. Reference1" and "exp. Reference2" is here only
> to demonstrate that the assumed references are wrong in the third table. "exp.
> Difference" is the column I want to get in the first place. I don't want to
> see
> Reference 1 or 2 at all in my solution (since I need a couple of those
> difference-columns).
>
> | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
> |--+-+-+-|
> |1 | 0 | 0 | 0 |
> |2 | 5 | 1 | 4 |
> |3 | 8 | 5 | 3 |
> |4 | 12 | 8 | 4 |
> |5 | 15 | 12 | 3 |
>
> This is what I get instead:
>
> | Line | actual Reference1 | actual Reference2 | Difference |
> |--+---+---+|
> |1 | 0 | 0 | 0 |
> |2 | 6 | 1 | 5 |
> |3 |12 | 1 | 11 |
> |4 |20 | 1 | 19 |
> |5 |30 | 1 | 29 |
>
> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@#$1))::$3=if(2 == @#, 0,
> remote(my-table,@#-1$1))::$4=if(2 == @#, 0,
> (remote(my-table,@#$1)-remote(my-table,@#-1$1)))
@# is the row number, so to refer to that row, you need @@#. Something like
this,
although I didn't test to see if everything gets parsed correctly:
#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0,
remote(my-table,@@#-1$1))::$4=if(2 == @#, 0,
(remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
Turning on formula debugging with `C-c {' helps.
--
Nick
"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler
[O] Calculate differences of remote table numbers
Hi!
I'd like to calculate the differences between rows of numbers of a
different table.
Here is a minimal example showing the issue:
#+NAME: my-table
| Numbers |
|-|
| 1 |
| 5 |
| 8 |
| 12 |
| 15 |
This is what I expected: "exp. Reference1" and "exp. Reference2" is here only
to demonstrate that the assumed references are wrong in the third table. "exp.
Difference" is the column I want to get in the first place. I don't want to see
Reference 1 or 2 at all in my solution (since I need a couple of those
difference-columns).
| Line | exp. Reference1 | exp. Reference2 | exp. Difference |
|--+-+-+-|
|1 | 0 | 0 | 0 |
|2 | 5 | 1 | 4 |
|3 | 8 | 5 | 3 |
|4 | 12 | 8 | 4 |
|5 | 15 | 12 | 3 |
This is what I get instead:
| Line | actual Reference1 | actual Reference2 | Difference |
|--+---+---+|
|1 | 0 | 0 | 0 |
|2 | 6 | 1 | 5 |
|3 |12 | 1 | 11 |
|4 |20 | 1 | 19 |
|5 |30 | 1 | 29 |
#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@#$1))::$3=if(2 == @#, 0,
remote(my-table,@#-1$1))::$4=if(2 == @#, 0,
(remote(my-table,@#$1)-remote(my-table,@#-1$1)))
As you can see, the "actual Reference1" are not the values from the first
column above (except the first one). "actual Reference2" is not the shifted
column 1 from above (except the first one). I have no clue, what these numbers
are.
Maybe you can help me (1) explaining what the numbers in actual Reference1 and
actual Reference2 are showing and (2) how to get the result ("expected
Difference"; without explicitly generating Reference 1 or 2).
Thanks!
--
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
> get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/
