Re: Table column formula with remote reference

[Wu Ming]

> On 15 Mar 2024, at 2:58 AM, Ihor Radchenko <yantar92@posteo.net> wrote:
> 
> Wu Ming <wu.ming2@icloud.com> writes:
> 
>>> See "Remote references" subsection. It explains that in
>>> remote(NAME,REF), REF is inside the remote table. Relative and current
>>> column/row is ambiguous there.
>>> 
>>> In contrast, @# and $# are special - they are replaced before
>>> remote(...) is processed.
>> ...
>> I have some trouble at understanding your answer. Do you mean @# refers a 
>> row on the table where the formula belongs and @0 refers a row on the remote 
>> table? Was tempted to describe the former as “current” but remote table is 
>> also current when accessed. A better noun may be needed.
> 
> Let me elaborate.
> 
> When Org mode sees something like
> 
> #+TBLFML: $1 = $2 + remote(A,@@#$1) 
> 
> 1. it goes to every cell in column 1 and remembers current column and
>   row numbers (original cell)
> 
> 2. In the right side of the formula $2 + remote(A,@@#$1), Org replaces
>   all the instances of @# and $# with current column and row.
>   So, when we are calculating the value for @1$1, we get
>   $2 + remote(A,@1$1)
> 
> 3. Org moves to table A and replaces remote(A,@1$1) with cell contents
>   of @1$1 inside table A. At this point, it is not allowed to have
>   relative references like $1 or $-1, because "current" column and row
>   are set inside remote table A - the original cell coordinates are not
>   available.
> 
> 4. Org goes back to the original table, takes the updated formula
>   $2 + <remote value A@1$1>, and replaces relative reference $2
>   according to the current column - with the value stored in @1$2
>   column
> 
> 5. Org passes the resulting expression <local value @1$2> + <remote
>   value A@1$1> to GNU cal and assigns the result as the value of the
>   current cell @1$1.
> 
> 6. Repeat for @2..$1 cells.
> 
> As you can see, @# and $# substitution always uses local cell
> coordinates. Any other relative reference is not allowed inside
> remote(...).
> 

Very clear now. Thank you. But I was mostly confounded by references $0 and #0 
versus the @@# (and $$#) you just described the processing of. Don’t want to 
abuse your time. I can figure it out when needed. But if you feel inclined to 
unravel this little detail of the manual as well I would clearly appreciate the 
effort. 

>> This made me worry about reliability of simple biz calculations I am trying 
>> on Org spreadsheet for the first time. Please advise.
> 
> Formula debugger is really helpful to understand the process.
> 
>> Finally I moved columns but now column numbers in formulas don’t relate to 
>> column order on display. How to understand which column formula affect which 
>> column?
> 
> Normally, if you use org-table-* commands, the formulas get updated when
> you move the columns.

One side effect of using remote formulas is re-organizing columns doesn’t 
update them automatically. I should find the balance of readability and 
formulas maintenance cost. But you may have suggested the solution below 
already with named columns.
> 
> To make things more readable, you can also assign names to columns:
> 
>     | ! |         |     P1 |     P2 |     P3 |   Tot |      |
>     |   | Maximum |     10 |     15 |     25 |    50 | 10.0 |
> 
> Then, you can write $P1 = ... instead of $3 = ...
> See "3.5.10 Advanced features" section of the manual.

Clever. And we are at the “Advanced“ features already. Are advanced-advanced in 
the realm of Calc? 

Asking because was also wondering how to optimize parameters (“solver”) and 
deal with locales (“,” vs “.” separators). For the latter I could possibly ‘tr’ 
them before sharing the output. But will possibly mess the alignment. Happened 
while trialling groff’s tbl.