From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp0.migadu.com ([2001:41d0:303:e16b::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms8.migadu.com with LMTPS id ILMtDu9b9mV6UAAAqHPOHw:P1 (envelope-from ) for ; Sun, 17 Mar 2024 03:56:47 +0100 Received: from aspmx1.migadu.com ([2001:41d0:303:e16b::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp0.migadu.com with LMTPS id ILMtDu9b9mV6UAAAqHPOHw (envelope-from ) for ; Sun, 17 Mar 2024 03:56:47 +0100 X-Envelope-To: larch@yhetil.org Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=icloud.com header.s=1a1hai header.b=M3yOWYW9; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org"; dmarc=pass (policy=quarantine) header.from=icloud.com ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1710644206; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references:list-id:list-help: list-unsubscribe:list-subscribe:list-post:dkim-signature; bh=Cw6FfsB//Nfeqlgx3r7rCB7WIfN4rttV/J0S86WRxtQ=; b=GTik6ONVRve+ZG9iiQD7+1Ul297G5SHn+V+yvphExQkq2m07F8CvOXyqd23B4wE6LZO2bf MWnIHW/jbMx1oi3yDB4ehlrNpsEYDNOXQpQmyh4TiiPdf5BrIatXSxVE7bBa9heEcTgdvV gxDwjGdveKlURdlh7Z6MTtTCOeHT4M8UZ/gy91mqtNGvQjhy/Vvt2m1HxfWW1lAeKrFuBP U24Sm+RTlEJZyDXwgzqco6qpNMvMCcdANI3N0nCKFbd6NWTowoIdUYN6kpsN24oCGpqeKJ DdeGZnPC39qGSoCKUnof5ztzkY57BAuEAhory9K71Ey8JUTxq8jxg7eJnKZCWA== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=icloud.com header.s=1a1hai header.b=M3yOWYW9; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org"; dmarc=pass (policy=quarantine) header.from=icloud.com ARC-Seal: i=1; s=key1; d=yhetil.org; t=1710644206; a=rsa-sha256; cv=none; b=A2DRw82hxWoiu8SStVIbrfuwzKOPdeSEnXX4Ia/KTt9odGNhDaG0k8NRPfXvoA2VMunk4h eSXdB7YBmjj/K5WZTFjfvNa+MevvNyP9q2xh6HKsv7B3vFNbhB/XbXEFQSsraXMb4K7ZTN qzbcwu5VTSTPoBBC8vBCCJrCGuBt7F9WqVA2zhUOgwv5OZbGNGqphTcFgX5LeLflgGobN9 y+maR7foOVKZABxCVisJpn07A243GPFKu8hmiWjE5teqcyQ9Js+zwYUKm+rMypZQ5vAodq I2lRLRFGUv9QAq2Uz/3GynfyeU5mbJFEocEnzk7ekblA93X4Y68Ybrf5bPwBIw== Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id BDFA314F22 for ; Sun, 17 Mar 2024 03:56:46 +0100 (CET) Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1rlggq-0005yh-Ip; Sat, 16 Mar 2024 22:56:00 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1rlggp-0005yU-2Z for emacs-orgmode@gnu.org; Sat, 16 Mar 2024 22:55:59 -0400 Received: from mr85p00im-zteg06021601.me.com ([17.58.23.187]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1rlggn-0004A5-Bv for emacs-orgmode@gnu.org; Sat, 16 Mar 2024 22:55:58 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1710644156; bh=Cw6FfsB//Nfeqlgx3r7rCB7WIfN4rttV/J0S86WRxtQ=; h=Content-Type:From:Mime-Version:Subject:Date:Message-Id:To; b=M3yOWYW9GeUAjKl3B58SNxu+K8ezZdmjj0QmsWQAAn+z8lSkjBoy1ehuNLYwHS540 /ZlvifnyW7CzakqXLcn4/ud3WVz6cXcxrmVcaHEKq4bruh8UIyQ/QvXf1L9N1D2eI0 RoTQxNnQyFa1sgU8BS3LkTeERutp6umA76JU9Jc7Bh2odCnR9D9LVlPSt3NZ8HNKUM 03Qo9oRxggYyiWBwu47tTcbzYIu9AdQtmZ/y0mTXWgcPI+Nn2cTB3ta3E2eDyAt72E /MXaJE9rROeuswlYkELhelZBjVkrmY1iwQt40q9CED660k/g+Io2iCFJvzvxQ5bROX jwy399TcRc+Gw== Received: from smtpclient.apple (mr38p00im-dlb-asmtp-mailmevip.me.com [17.57.152.18]) by mr85p00im-zteg06021601.me.com (Postfix) with ESMTPSA id CC0A8305834D for ; Sun, 17 Mar 2024 02:55:55 +0000 (UTC) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: Wu Ming Mime-Version: 1.0 (1.0) Subject: Re: Table column formula with remote reference Date: Sun, 17 Mar 2024 10:55:51 +0800 Message-Id: <0E2BEC9E-15B2-4FCB-9890-6BAD6B8B7546@icloud.com> References: <87sf0sh3w7.fsf@localhost> In-Reply-To: <87sf0sh3w7.fsf@localhost> To: emacs-orgmode@gnu.org X-Mailer: iPhone Mail (19H380) X-Proofpoint-GUID: 4n9Q8HNR7mULfBnN69IBR8JJLmTV__ZQ X-Proofpoint-ORIG-GUID: 4n9Q8HNR7mULfBnN69IBR8JJLmTV__ZQ X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1011,Hydra:6.0.619,FMLib:17.11.176.26 definitions=2024-03-16_21,2024-03-15_01,2023-05-22_02 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 mlxlogscore=999 adultscore=0 bulkscore=0 clxscore=1015 spamscore=0 phishscore=0 malwarescore=0 suspectscore=0 mlxscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2308100000 definitions=main-2403170021 Received-SPF: pass client-ip=17.58.23.187; envelope-from=wu.ming2@icloud.com; helo=mr85p00im-zteg06021601.me.com X-Spam_score_int: -25 X-Spam_score: -2.6 X-Spam_bar: -- X-Spam_report: (-2.6 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, FREEMAIL_ENVFROM_END_DIGIT=0.25, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H2=-0.001, SPF_HELO_NONE=0.001, SPF_PASS=-0.001, T_SCC_BODY_TEXT_LINE=-0.01 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: emacs-orgmode-bounces+larch=yhetil.org@gnu.org X-Migadu-Flow: FLOW_IN X-Migadu-Country: US X-Migadu-Spam-Score: -9.34 X-Spam-Score: -9.34 X-Migadu-Queue-Id: BDFA314F22 X-Migadu-Scanner: mx13.migadu.com X-TUID: TN+N47adSR3M > On 15 Mar 2024, at 2:58 AM, Ihor Radchenko wrote: >=20 > =EF=BB=BFWu Ming writes: >=20 >>> 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. >>>=20 >>> 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 remot= e table? Was tempted to describe the former as =E2=80=9Ccurrent=E2=80=9D but= remote table is also current when accessed. A better noun may be needed. >=20 > Let me elaborate. >=20 > When Org mode sees something like >=20 > #+TBLFML: $1 =3D $2 + remote(A,@@#$1)=20 >=20 > 1. it goes to every cell in column 1 and remembers current column and > row numbers (original cell) >=20 > 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) >=20 > 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. >=20 > 4. Org goes back to the original table, takes the updated formula > $2 + , and replaces relative reference $2 > according to the current column - with the value stored in @1$2 > column >=20 > 5. Org passes the resulting expression + value A@1$1> to GNU cal and assigns the result as the value of the > current cell @1$1. >=20 > 6. Repeat for @2..$1 cells. >=20 > As you can see, @# and $# substitution always uses local cell > coordinates. Any other relative reference is not allowed inside > remote(...). >=20 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=E2=80=99= t want to abuse your time. I can figure it out when needed. But if you feel i= nclined to unravel this little detail of the manual as well I would clearly a= ppreciate the effort.=20 >> This made me worry about reliability of simple biz calculations I am tryi= ng on Org spreadsheet for the first time. Please advise. >=20 > Formula debugger is really helpful to understand the process. >=20 >> Finally I moved columns but now column numbers in formulas don=E2=80=99t r= elate to column order on display. How to understand which column formula aff= ect which column? >=20 > 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=E2=80= =99t update them automatically. I should find the balance of readability and= formulas maintenance cost. But you may have suggested the solution below al= ready with named columns. >=20 > To make things more readable, you can also assign names to columns: >=20 > | ! | | P1 | P2 | P3 | Tot | | > | | Maximum | 10 | 15 | 25 | 50 | 10.0 | >=20 > Then, you can write $P1 =3D ... instead of $3 =3D ... > See "3.5.10 Advanced features" section of the manual. Clever. And we are at the =E2=80=9CAdvanced=E2=80=9C features already. Are a= dvanced-advanced in the realm of Calc?=20 Asking because was also wondering how to optimize parameters (=E2=80=9Csolve= r=E2=80=9D) and deal with locales (=E2=80=9C,=E2=80=9D vs =E2=80=9C.=E2=80=9D= separators). For the latter I could possibly =E2=80=98tr=E2=80=99 them befo= re sharing the output. But will possibly mess the alignment. Happened while t= rialling groff=E2=80=99s tbl.=