how to add a range of columns

how to add a range of columns

[Uwe Brauer]

Hi 

I'd like to add a range values of two columns something like this 

#+begin_src elisp

#+Name: check
| User1 | User2 | Result |
|-------+-------+--------|
|     1 |     3 | [4]    |
|     4 |     8 | [4]    |
|     9 |     3 | [4]    |
|-------+-------+--------|
|     7 |     9 | [4]    |
#+TBLFM: @I$3..@II$3=@I$1..@2$1+@I$2..@II$2
#+end_src


But it does not work, so I am a bit puzzled. Any advice would be
welcome.

Uwe Brauer 

Re: how to add a range of columns

[Eric S Fraga]

On Friday,  2 Jul 2021 at 10:42, Uwe Brauer wrote:
> I'd like to add a range values of two columns something like this 

I am not sure what you are trying to achieve.  Would you please explain
in more detail?  It kind of looks like you are trying to add columns 1
and 2 to get column 3?  If so, you could simply have:

#+TBLFM: $3=$1+$2

so I may have misunderstood.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: how to add a range of columns

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1124 bytes --]

>>> "ESF" == Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Friday,  2 Jul 2021 at 10:42, Uwe Brauer wrote:
>> I'd like to add a range values of two columns something like this 

> I am not sure what you are trying to achieve.  Would you please explain
> in more detail?  It kind of looks like you are trying to add columns 1
> and 2 to get column 3?  If so, you could simply have:

> #+TBLFM: $3=$1+$2

No I only want to add the parts of column $1 and $2 that are between the
two hlines (I wanted to use hline @I and @II and not the explict row
numbers, since this is more convenient. But it does to work with
explicit row numbers 

I added a column to indicate what I want to add and want not. Is this
better explained?

#+begin_src elisp

#+Name: check
| User1 | User2 | Result | don't add |
|-------+-------+--------+-----------|
|     1 |     3 | [4]    | add       |
|     4 |     8 | [4]    | add       |
|     9 |     3 | [4]    | add       |
|-------+-------+--------+-----------|
|     7 |     9 | [4]    | don't add |
#+TBLFM: @I$3..@II$3=@I$1..@2$1+@I$2..@II$2
#+end_src






[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: how to add a range of columns

[Eric S Fraga]

On Friday,  2 Jul 2021 at 11:23, Uwe Brauer wrote:
> No I only want to add the parts of column $1 and $2 that are between the
> two hlines

Okay; problem 1 is that org formulas are not really vector based: a
range on the left hand side says that each element in the range will be
assigned the outcome of the right hand side so the right hand side
should not be a vector.

> (I wanted to use hline @I and @II and not the explict row
> numbers, since this is more convenient. But it does to work with
> explicit row numbers 

Yes, you cannot use @I etc. in the range definition for the target.

I can think of 2 options.  First, you can use relative addressing, i.e.:

#+TBLFM: @<<$3..@>>$3=$1+$2

where @<< means the second row and @>> the second last row.

Alternatively, you can introduce a new column at the start and use the
"Advanced Features" of the spreadsheet described in the info manual
section

(org) Advanced features

which will allow you to specify which rows should be evaluated (see the
* option).

HTH,
eric

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: how to add a range of columns

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1270 bytes --]

>>> "ESF" == Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Friday,  2 Jul 2021 at 11:23, Uwe Brauer wrote:
>> No I only want to add the parts of column $1 and $2 that are between the
>> two hlines

> Okay; problem 1 is that org formulas are not really vector based: a
> range on the left hand side says that each element in the range will be
> assigned the outcome of the right hand side so the right hand side
> should not be a vector.

>> (I wanted to use hline @I and @II and not the explict row
>> numbers, since this is more convenient. But it does to work with
>> explicit row numbers 

> Yes, you cannot use @I etc. in the range definition for the target.

> I can think of 2 options.  First, you can use relative addressing, i.e.:

> #+TBLFM: @<<$3..@>>$3=$1+$2

> where @<< means the second row and @>> the second last row.

Thanks, that was helpful

> Alternatively, you can introduce a new column at the start and use the
> "Advanced Features" of the spreadsheet described in the info manual
> section

> (org) Advanced features

> which will allow you to specify which rows should be evaluated (see the
> * option).

I will have a look thanks (I already thought about commenting out
unwanted rows, but that looked ugly).

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: how to add a range of columns

[Eric S Fraga]

On Friday,  2 Jul 2021 at 12:04, Uwe Brauer wrote:
> I will have a look thanks (I already thought about commenting out
> unwanted rows, but that looked ugly).

The nice thing about the advanced features is they give you full control
and none of the extra notation is exported.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: how to add a range of columns

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 648 bytes --]

>>> "ESF" == Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Friday,  2 Jul 2021 at 12:04, Uwe Brauer wrote:
>> I will have a look thanks (I already thought about commenting out
>> unwanted rows, but that looked ugly).

> The nice thing about the advanced features is they give you full control
> and none of the extra notation is exported.

Ok, I checked https://orgmode.org/manual/Advanced-features.html

I cannot see at the moment how your solution 

#+TBLFM: @<<$3..@>>$3=$1+$2

Could be replaced with the syntax explained in this page.

The problem is the target @<<$3..@>>$3

There seems no option to specify such a range.

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: how to add a range of columns

[Eric S Fraga]

On Friday,  2 Jul 2021 at 16:07, Uwe Brauer wrote:
> The problem is the target @<<$3..@>>$3
>
> There seems no option to specify such a range.

Sorry, I didn't explain myself properly.  You can specify which rows
should be calculated automatically so that you can then use a simple
column formula without it extending to the full column:

#+begin_src org
  ,#+Name: check
  |   | User1 | User2 | Result |
  |---+-------+-------+--------|
  | # |     1 |     3 |      4 |
  | # |     4 |     8 |     12 |
  | # |    10 |     3 |     13 |
  |---+-------+-------+--------|
  |   |     7 |     9 |        |
  ,#+TBLFM: $4=$2+$3
#+end_src

If you type "C-u C-c *" to re-calculate the table, the last entry in the
4th column is not updated even though the expression says that column 4
is the sum of columns 2 and 3.

Once you start using some of these advanced features, you can make your
formulas easier to read, e.g.:

#+begin_src org
  ,#+Name: check2
  | ! | User1 | User2 | Result |
  |---+-------+-------+--------|
  | # |     1 |     3 |      4 |
  | # |     4 |     8 |     12 |
  | # |    10 |     3 |     13 |
  |---+-------+-------+--------|
  |   |     7 |     9 |        |
  ,#+TBLFM: $4=$User1+$User2
#+end_src

HTH,
eric

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: how to add a range of columns

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 2868 bytes --]

>>> "ESF" == Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Friday,  2 Jul 2021 at 16:07, Uwe Brauer wrote:
>> The problem is the target @<<$3..@>>$3
>> 
>> There seems no option to specify such a range.

> Sorry, I didn't explain myself properly.  You can specify which rows
> should be calculated automatically so that you can then use a simple
> column formula without it extending to the full column:

> #+begin_src org
>   ,#+Name: check
>   |   | User1 | User2 | Result |
>   |---+-------+-------+--------|
>   | # |     1 |     3 |      4 |
>   | # |     4 |     8 |     12 |
>   | # |    10 |     3 |     13 |
>   |---+-------+-------+--------|
>   |   |     7 |     9 |        |
>   ,#+TBLFM: $4=$2+$3
> #+end_src


> If you type "C-u C-c *" to re-calculate the table, the last entry in the
> 4th column is not updated even though the expression says that column 4
> is the sum of columns 2 and 3.

> Once you start using some of these advanced features, you can make your
> formulas easier to read, e.g.:

> #+begin_src org
>   ,#+Name: check2
>   | ! | User1 | User2 | Result |
>   |---+-------+-------+--------|
>   | # |     1 |     3 |      4 |
>   | # |     4 |     8 |     12 |
>   | # |    10 |     3 |     13 |
>   |---+-------+-------+--------|
>   |   |     7 |     9 |        |
>   ,#+TBLFM: $4=$User1+$User2
> #+end_src

Thanks, I thought of that 

#+begin_src elisp
#+Name: check3
  |   | User1 | User2 | Result |
  |---+-------+-------+--------|
  |   |     1 |     3 | 4      |
  |   |     4 |     8 | 12     |
  |   |    10 |     3 | 13     |
  |---+-------+-------+--------|
  | / |     7 |     9 |        |
#+TBLFM: $4=$2+$3
#+end_src
which is a bit the antipode of your approach.

It just occurred to me that this approach has its limitations, let's go
step further 

#+begin_src elisp
#+Name: check2
  | ! | User1 | User2 | Result | New | Actual |
  |---+-------+-------+--------+-----+--------|
  | # |     1 |     3 |      4 |   6 |        |
  | # |     4 |     8 |     12 |   9 |        |
  | # |    10 |     3 |     13 |  10 |        |
  |---+-------+-------+--------+-----+--------|
  |   |     7 |     9 |        |     |        |
#+TBLFM: $4=$User1+$User2
#+end_src


Now I want to add also result and new, but only the first two rows, the
only way to do it seems to be your original approach.


#+begin_src elisp

#+Name: check2
  | ! | User1 | User2 | Result | New | Actual |
  |---+-------+-------+--------+-----+--------|
  | # |     1 |     3 |      4 |   6 |     10 |
  | # |     4 |     8 |     12 |   9 |     21 |
  | # |    10 |     3 |     13 |  10 |        |
  |---+-------+-------+--------+-----+--------|
  |   |     7 |     9 |        |     |        |
#+TBLFM: $4=$User1+$User2::@<<$6..@>>>$6=$4+$5
#+end_src

Regards

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: how to add a range of columns

[Eric S Fraga]

On Friday,  2 Jul 2021 at 20:56, Uwe Brauer wrote:
> Now I want to add also result and new, but only the first two rows, the
> only way to do it seems to be your original approach.

At this point, you need to start using more advanced formulas,
e.g. involving conditionals, maybe.  You could do arithmetic on the
column and row indices ($# and @# respectively) and apply a specific
formula if the right conditions are met.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: how to add a range of columns

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 611 bytes --]

>>> "ESF" == Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Friday,  2 Jul 2021 at 20:56, Uwe Brauer wrote:
>> Now I want to add also result and new, but only the first two rows, the
>> only way to do it seems to be your original approach.

> At this point, you need to start using more advanced formulas,
> e.g. involving conditionals, maybe.  You could do arithmetic on the
> column and row indices ($# and @# respectively) and apply a specific
> formula if the right conditions are met.

Right, I just realised that I can have recursion in conditionals, which
is a great help. I did not realise that before.

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]