Hello, I'm trying to create a table where the third column between two hlines is set by a formula referencing the second column. Here is an example: | a | b | c | |-------+-------+--------| | row 1 | 12.00 | 144.00 | | row 2 | | 23.00 | |-------+-------+--------| | sum | 12.00 | 167.00 | #+TBLFM: @I$3..II$3=if($2 != 0, 12*$2, $3);%.2f::@4$2=vsum(@I..II);%.2f::@4$3=vsum(@I..II);%.2f When I try to calculate this table however, I get the error "Can’t assign to hline relative reference". Why is this now allowed? This seems like a fairly mundane use of a table to me. It is explicitly disabled in the source, but without explanation, and searching the mailing list doesn't turn up many answers. Is there a recommended way to handle a situation like this? Could this feature be fixed? Thank you, Dante
Hello, I'm trying to create a table where the third column between two hlines is set by a formula referencing the second column. Here is an example: | a | b | c | |-------+-------+--------| | row 1 | 12.00 | 144.00 | | row 2 | | 23.00 | |-------+-------+--------| | sum | 12.00 | 167.00 | #+TBLFM: @I$3..II$3=if($2 != 0, 12*$2, $3);%.2f::@4$2=vsum(@I..II);%.2f::@4$3=vsum(@I..II);%.2f When I try to calculate this table however, I get the error "Can’t assign to hline relative reference". Why is this now allowed? This seems like a fairly mundane use of a table to me. It is explicitly disabled in the source, but without explanation, and searching the mailing list doesn't turn up many answers. Is there a recommended way to handle a situation like this? Could this feature be fixed? Thank you, Dante
[-- Attachment #1: Type: text/plain, Size: 1518 bytes --] This seems to be related to: https://emacs.stackexchange.com/questions/15185/cant-assign-to-hline-relative-reference There seem to be restrictions regarding hline-relative-references within table-formulas. If change 'I' and 'II' for 1 and 2 on the very left of the formula, it works (keeping the other hline-relative parts) . If I stay with the former I get the same error as you (emacs 26.1, orgmode 9.1.9, Debian stable). I hope this can help, Daniel Am 21. Oktober 2020 03:14:10 MESZ schrieb Dante Catalfamo <dante@lambda.cx>: >Hello, > >I'm trying to create a table where the third column between two hlines >is set by a formula referencing the second column. > >Here is an example: > >| a | b | c | >|-------+-------+--------| >| row 1 | 12.00 | 144.00 | >| row 2 | | 23.00 | >|-------+-------+--------| >| sum | 12.00 | 167.00 | >#+TBLFM: @I$3..II$3=if($2 != 0, 12*$2, >$3);%.2f::@4$2=vsum(@I..II);%.2f::@4$3=vsum(@I..II);%.2f > >When I try to calculate this table however, I get the error "Can’t >assign to hline relative reference". > >Why is this now allowed? This seems like a fairly mundane use of a >table to me. It is explicitly disabled in the source, but without >explanation, and searching the mailing list doesn't turn up many >answers. > >Is there a recommended way to handle a situation like this? > >Could this feature be fixed? > >Thank you, >Dante -- Diese Nachricht wurde von meinem Android-Gerät mit K-9 Mail gesendet. [-- Attachment #2: Type: text/html, Size: 1987 bytes --]
[-- Attachment #1: Type: text/plain, Size: 2280 bytes --] In the case where I'm using this formula, there are many more rows between @I..@II. I only used two in the example to keep the email small. I would also be adding more rows regularly, making it slightly impractical to be adjusting the beginning and end points of the formula every time I add or remove a row. I appreciate your suggestion, though I was hoping for a more convenient solution. If nothing else works, that's what I'll do. I'd like to get to the bottom of why hlines aren't permitted in table formulas, it seems like something that should be allowed. Thanks, Dante ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Wednesday, October 21, 2020 8:43 AM, Daniel Herzig <daniel.herzig@outlook.at> wrote: > This seems to be related to: https://emacs.stackexchange.com/questions/15185/cant-assign-to-hline-relative-reference > > There seem to be restrictions regarding hline-relative-references within table-formulas. > > If change 'I' and 'II' for 1 and 2 on the very left of the formula, it works (keeping the other hline-relative parts) . If I stay with the former I get the same error as you (emacs 26.1, orgmode 9.1.9, Debian stable). > > I hope this can help, > Daniel > > Am 21. Oktober 2020 03:14:10 MESZ schrieb Dante Catalfamo <dante@lambda.cx>: > >> Hello, >> >> I'm trying to create a table where the third column between two hlines is set by a formula referencing the second column. >> >> Here is an example: >> >> | a | b | c | >> >> |-------+-------+--------| >> >> | row 1 | 12.00 | 144.00 | >> >> | row 2 | | 23.00 | >> >> |-------+-------+--------| >> >> | sum | 12.00 | 167.00 | >> >> #+TBLFM: @I$3..II$3=if($2 != 0, 12*$2, $3);%.2f::@4$2=vsum(@I..II);%.2f::@4$3=vsum(@I..II);%.2f >> >> When I try to calculate this table however, I get the error "Can’t assign to hline relative reference". >> >> Why is this now allowed? This seems like a fairly mundane use of a table to me. It is explicitly disabled in the source, but without explanation, and searching the mailing list doesn't turn up many answers. >> >> Is there a recommended way to handle a situation like this? >> >> Could this feature be fixed? >> >> Thank you, >> >> Dante > > -- > Diese Nachricht wurde von meinem Android-Gerät mit K-9 Mail gesendet. [-- Attachment #2: Type: text/html, Size: 3607 bytes --]
Dante Catalfamo wrote:
> In the case where I'm using this formula, there are many more rows
> between @I..@II. I only used two in the example to keep the email small.
> I would also be adding more rows regularly, making it slightly
> impractical to be adjusting the beginning and end points of the formula
> every time I add or remove a row. I appreciate your suggestion, though I
> was hoping for a more convenient solution. If nothing else works, that's
> what I'll do.
You are not alone. From my point of view it is not clear enough from org
manual that @I references could be used only to the right of "=". The
error message was a bad surprize. Another similar pitfal was with named
columns.
However in some cases @<< (unlike @2 remains unchanged if a new line is
added before second row) and @>> could be a workaround. If there are
several horizontal lines, sometimes it is possible to mark particular
lines with # and use e.g. $3=vsum(@-I$-1..@+I$-1) to avoid proliferation
of same formulas for each table section.
That seems to work for the most part, but now I'm experiencing a strange
difference when evaluating. If I use a formula like
#+TBLFM:@2$3..@23$3=if($2!=0,12*$2,$3);%.2f::@24$2=vsum(@I..@II);%.2f::@24$3=vsum(@I..@II);%.2f
The column gets evaluated first, then the sum at the bottom gets
evaluated, resulting in what I'd expect. But if I use a formula like
#+TBLFM:@<<$3..@>>$3=if($2!=0,12*$2,$3);%.2f::@24$2=vsum(@I..@II);%.2f::@24$3=vsum(@I..@II);%.2f
The sum gets calculated first, and the column after, meaning the sum
doesn't reflect any changes made before calculation. I find this pretty
strange considering the formulas are in the same order. Do formulas
involving relative references get calculated last for some reason?
On 2020-10-22 8:04 a.m., Maxim Nikulin wrote:
>
> Dante Catalfamo wrote:
>> In the case where I'm using this formula, there are many more rows
>> between @I..@II. I only used two in the example to keep the email small.
>> I would also be adding more rows regularly, making it slightly
>> impractical to be adjusting the beginning and end points of the formula
>> every time I add or remove a row. I appreciate your suggestion, though I
>> was hoping for a more convenient solution. If nothing else works, that's
>> what I'll do.
>
> You are not alone. From my point of view it is not clear enough from org
> manual that @I references could be used only to the right of "=". The
> error message was a bad surprize. Another similar pitfal was with named
> columns.
>
> However in some cases @<< (unlike @2 remains unchanged if a new line is
> added before second row) and @>> could be a workaround. If there are
> several horizontal lines, sometimes it is possible to mark particular
> lines with # and use e.g. $3=vsum(@-I$-1..@+I$-1) to avoid proliferation
> of same formulas for each table section.
>
>
2020-10-23 Dante Catalfamo wrote: > That seems to work for the most part, but now I'm experiencing a strange > difference when evaluating. If I use a formula like > > #+TBLFM:@2$3..@23$3=if($2!=0,12*$2,$3);%.2f::@24$2=vsum(@I..@II);%.2f::@24$3=vsum(@I..@II);%.2f > > The column gets evaluated first, then the sum at the bottom gets > evaluated, resulting in what I'd expect. But if I use a formula like > > #+TBLFM:@<<$3..@>>$3=if($2!=0,12*$2,$3);%.2f::@24$2=vsum(@I..@II);%.2f::@24$3=vsum(@I..@II);%.2f > > The sum gets calculated first, and the column after, meaning the sum > doesn't reflect any changes made before calculation. I find this pretty > strange considering the formulas are in the same order. Do formulas > involving relative references get calculated last for some reason? Interesting... However my curiosity is not strong enough to find appropriate place in the code. Either I never tried such combination of references (I use @>$2=... for the last row) or I have not expected reliable evaluation order and just hit C-c C-c several times. It seems that single cell formulas are calculated later than ranges | a | c | b | |---+---+---| | 2 | | | | 3 | | | | 4 | | | | 5 | | | | 6 | | | | 7 | | | |---+---+---| | | | | #+TBLFM: @<<$3..@>>$3=$1*2 :: @<<$2..@<<<$2=$3*3 :: @5$2..@6$2=$3*4 :: @4$2=$3*5 :: @>>$2=$3*6 | a | c | b | |---+----+----| | 2 | 0 | 4 | | 3 | 0 | 6 | | 4 | 0 | 8 | | 5 | 0 | 10 | | 6 | 0 | 12 | | 7 | 84 | 14 | |---+----+----| | | | | #+TBLFM: @<<$3..@>>$3=$1*2 :: @<<$2..@<<<$2=$3*3 :: @5$2..@6$2=$3*4 :: @4$2=$3*5 :: @>>$2=$3*6 Notice zeroes for @<<..@<<< but not for @>>. Even more funny that @>>>>>>>$2..@>>>>>>$2=$3 instead of @<<$3..@<<<$3 give non-zero values for the second and third rows. Anyway I do not plan to memorize such rules. By the way, I have noticed that @<<$3..@>>$3=... likely could be replaced by just $3=... and it is evaluated quite early in such form. Skimming through the manual previous times, I missed the point of "column formulas" https://orgmode.org/manual/Column-formulas.html or [[info:org#Column formulas]] and have noticed the details just today.