Bug: Can’t assign to hline relative reference

Bug: Can’t assign to hline relative reference

[Dante Catalfamo]

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

Bug: Can’t assign to hline relative reference

[Dante Catalfamo]

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

Re: Bug: Can’t assign to hline relative reference

[Daniel Herzig]

[-- 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 --]

Re: Bug: Can’t assign to hline relative reference

[Dante Catalfamo]

[-- 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 --]

Re: Bug: Can’t assign to hline relative reference

[Maxim Nikulin]

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.

Re: Bug: Can’t assign to hline relative reference

[Dante Catalfamo]

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.
> 
> 

Re: Bug: Can’t assign to hline relative reference

[Maxim Nikulin]

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.