From: Wu Ming <wu.ming2@icloud.com>
To: emacs-orgmode@gnu.org
Subject: Re: Table column formula with remote reference
Date: Sun, 17 Mar 2024 10:55:51 +0800 [thread overview]
Message-ID: <0E2BEC9E-15B2-4FCB-9890-6BAD6B8B7546@icloud.com> (raw)
In-Reply-To: <87sf0sh3w7.fsf@localhost>
> 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.
next prev parent reply other threads:[~2024-03-17 2:56 UTC|newest]
Thread overview: 12+ messages / expand[flat|nested] mbox.gz Atom feed top
2024-03-11 10:52 Table column formula with remote reference Wu Ming
2024-03-11 11:43 ` Wu Ming
2024-03-12 14:46 ` Ihor Radchenko
2024-03-13 6:04 ` Wu Ming
2024-03-13 12:16 ` Ihor Radchenko
2024-03-14 1:16 ` Wu Ming
2024-03-14 13:40 ` Fraga, Eric
2024-03-17 2:29 ` Wu Ming
2024-03-18 12:53 ` Fraga, Eric
2024-03-14 21:58 ` Ihor Radchenko
2024-03-17 2:55 ` Wu Ming [this message]
2024-03-17 14:03 ` Ihor Radchenko
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=0E2BEC9E-15B2-4FCB-9890-6BAD6B8B7546@icloud.com \
--to=wu.ming2@icloud.com \
--cc=emacs-orgmode@gnu.org \
/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).