Re: Table column formula with remote reference
> On 15 Mar 2024, at 2:58 AM, Ihor Radchenko wrote: > > Wu Ming 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 + , and replaces relative reference $2 > according to the current column - with the value stored in @1$2 > column > > 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. > > 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.
Re: Table column formula with remote reference
> On 14 Mar 2024, at 9:40 PM, Fraga, Eric wrote: > > On Thursday, 14 Mar 2024 at 09:16, Wu Ming wrote: >> Unrelated, but appeared on the same trial, noticed a cell was >> mis-calculated. [...] This made me worry about reliability of simple >> biz calculations I am trying on Org spreadsheet for the first >> time. Please advise. > > I've not seen any problems with spreadsheet/table calculations in org and use > it extensively. I don't use remote access generally however. > > In any case, one very nice feature of org tables is you can see exactly how > and what it calculates when you ask it to. Turn on debugging by "C-c {" > (org-table-toggle-formula-debugger) and you can see all the information you > should need to identify what, if anything, is going wrong. > > Turn off debugging with the same key sequence. Thanks for the reference to formula debugger. In the heat of debugging an error as obvious, and worrying, as the one I saw forgot about it. Though I am still new to Emacs and Org so that’s not so surprising. I have one table retrieving data from two more. 18 columns x 7 rows total. I could have everything into one larger table but splitting makes them more readable I think. And possibly simplifies sharing end results. Haven’t tried Org export options yet. What is your organization system with tables?
Re: Table column formula with remote reference
> On 13 Mar 2024, at 20:16, Ihor Radchenko wrote: > > Wu Ming writes: > >> My fault. Missed the line "One of the very first actions during evaluation >> of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the >> formula with the row or column number of the field where the current result >> will go to.“ So '@@#' becomes '@'. >> >> Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to >> the current row and column, respectively, i.e., to the row/column for the >> field being computed.” and did try '@0$1'. Why is this different from the >> above? > > 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 agree that the manual is somewhat confusing. Possibly, we may even > change `org-table-get-remote-range' to use relative references according > to the original table. Improvements welcome! > 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. Unrelated, but appeared on the same trial, noticed a cell was mis-calculated. Could not pin-point the reason before error disappeared after running a side formula performing the same operation. Formula was simply copying column values from a remote table. Remote table values were never changed. Recalculate all and C-c C-c were performed multiple times before the curious fix. This made me worry about reliability of simple biz calculations I am trying on Org spreadsheet for the first time. Please advise. 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? Sorry to coalesce multiple questions in to one. They just came to me while typing.
Re: Table column formula with remote reference
> On 12 Mar 2024, at 22:46, Ihor Radchenko wrote: > > Wu Ming writes: > >> Problem solved with >> >> $1 = remote(A,@@#$1) >> >> as described on the manual page >> https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 >> <https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1> >> >> Where is the coordinate @@# documented? > > I am confused. Isn't it documented in the linked section of the manual? > > > -- > Ihor Radchenko // yantar92, > Org mode contributor, > Learn more about Org mode at <https://orgmode.org/>. > Support Org development at <https://liberapay.com/org-mode>, > or support my work at <https://liberapay.com/yantar92> My fault. Missed the line "One of the very first actions during evaluation of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula with the row or column number of the field where the current result will go to.“ So '@@#' becomes '@'. Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to the current row and column, respectively, i.e., to the row/column for the field being computed.” and did try '@0$1'. Why is this different from the above?
Table column formula with remote reference
Hello, #+NAME: A Table with header row here #+NAME: B Table with header row here #+TBLFML: $1 = remote(A,$1) Does nothing. $1 = remote(A,@0) referring current row w implicit column does not work. Debug shows on row 2 of current table it returns the value of row 1, the header, on table A. Same result as above with @0$1 instead. $1 = remote(A,@2$1) does what expected. For every row copies the same value of @2$1 from table A to table B. Need to refer multiple columns on different tables in a column formula for a final table. Please advise. Thanks.
Re: Table column formula with remote reference
Problem solved with $1 = remote(A,@@#$1) as described on the manual page https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 <https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1> Where is the coordinate @@# documented? > On 11 Mar 2024, at 18:52, Wu Ming wrote: > > Hello, > > #+NAME: A > Table with header row here > > #+NAME: B > Table with header row here > #+TBLFML: $1 = remote(A,$1) > > Does nothing. > > $1 = remote(A,@0) referring current row w implicit column does not work. > Debug shows on row 2 of current table it returns the value of row 1, the > header, on table A. > > Same result as above with @0$1 instead. > > $1 = remote(A,@2$1) does what expected. For every row copies the same value > of @2$1 from table A to table B. > > Need to refer multiple columns on different tables in a column formula for a > final table. > > Please advise. Thanks. > >