emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* Bug: Can’t assign to hline relative reference
@ 2020-10-20 21:39 Dante Catalfamo
  0 siblings, 0 replies; 7+ messages in thread
From: Dante Catalfamo @ 2020-10-20 21:39 UTC (permalink / raw)
  To: emacs-orgmode@gnu.org

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


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Bug: Can’t assign to hline relative reference
@ 2020-10-21  1:14 Dante Catalfamo
  2020-10-21 12:43 ` Daniel Herzig
  0 siblings, 1 reply; 7+ messages in thread
From: Dante Catalfamo @ 2020-10-21  1:14 UTC (permalink / raw)
  To: emacs-orgmode@gnu.org

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



^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: Bug: Can’t assign to hline relative reference
  2020-10-21  1:14 Bug: Can’t assign to hline relative reference Dante Catalfamo
@ 2020-10-21 12:43 ` Daniel Herzig
  2020-10-21 16:14   ` Dante Catalfamo
  0 siblings, 1 reply; 7+ messages in thread
From: Daniel Herzig @ 2020-10-21 12:43 UTC (permalink / raw)
  To: Dante Catalfamo, emacs-orgmode@gnu.org

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

^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: Bug: Can’t assign to hline relative reference
  2020-10-21 12:43 ` Daniel Herzig
@ 2020-10-21 16:14   ` Dante Catalfamo
  2020-10-22 12:04     ` Maxim Nikulin
  0 siblings, 1 reply; 7+ messages in thread
From: Dante Catalfamo @ 2020-10-21 16:14 UTC (permalink / raw)
  To: Daniel Herzig; +Cc: emacs-orgmode@gnu.org

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

^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: Bug: Can’t assign to hline relative reference
  2020-10-21 16:14   ` Dante Catalfamo
@ 2020-10-22 12:04     ` Maxim Nikulin
  2020-10-22 19:56       ` Dante Catalfamo
  0 siblings, 1 reply; 7+ messages in thread
From: Maxim Nikulin @ 2020-10-22 12:04 UTC (permalink / raw)
  To: emacs-orgmode

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.



^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: Bug: Can’t assign to hline relative reference
  2020-10-22 12:04     ` Maxim Nikulin
@ 2020-10-22 19:56       ` Dante Catalfamo
  2020-10-29 12:40         ` Maxim Nikulin
  0 siblings, 1 reply; 7+ messages in thread
From: Dante Catalfamo @ 2020-10-22 19:56 UTC (permalink / raw)
  To: emacs-orgmode

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



^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: Bug: Can’t assign to hline relative reference
  2020-10-22 19:56       ` Dante Catalfamo
@ 2020-10-29 12:40         ` Maxim Nikulin
  0 siblings, 0 replies; 7+ messages in thread
From: Maxim Nikulin @ 2020-10-29 12:40 UTC (permalink / raw)
  To: emacs-orgmode

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.



^ permalink raw reply	[flat|nested] 7+ messages in thread

end of thread, other threads:[~2020-10-29 12:57 UTC | newest]

Thread overview: 7+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2020-10-21  1:14 Bug: Can’t assign to hline relative reference Dante Catalfamo
2020-10-21 12:43 ` Daniel Herzig
2020-10-21 16:14   ` Dante Catalfamo
2020-10-22 12:04     ` Maxim Nikulin
2020-10-22 19:56       ` Dante Catalfamo
2020-10-29 12:40         ` Maxim Nikulin
  -- strict thread matches above, loose matches on Subject: below --
2020-10-20 21:39 Dante Catalfamo

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