Re: [O] Calculate differences of remote table numbers

[Nick Dokos]

Karl Voit <address@hidden> 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(address@hidden, 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