* problem with empty column
@ 2018-01-05 8:28 Uwe Brauer
2018-01-05 9:25 ` Loris Bennett
2018-01-05 9:35 ` Eric S Fraga
0 siblings, 2 replies; 9+ messages in thread
From: Uwe Brauer @ 2018-01-05 8:28 UTC (permalink / raw)
To: emacs-orgmode
Hi
Consider please the following table
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----|
| Entry1 | | | | | NP |
| Entry2 | 10 | 20 | 30 | 40 | 10 |
#+TBLFM: $6=if("$2" == "nan" , string("NP"),($2+$3+$4+$5)/10);E
The last column contains a string NP if the corresponding cell in the
second column is empty.
However when I consider
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----|
| Entry1 | | | | | NP |
| Entry2 | 10 | 20 | 30 | 40 | 10 |
| Entry3 | 10 | | 20 | 30 | nan |
#+TBLFM: $6=if("$2" == "nan" , string("NP"),($2+$3+$4+$5)/10);E
The last row behaves strangely the cell in the second column is *not*
empty but the cell in the third one is, nevertheless the cell in the
last column contains the string nan, which I don't understand.
Any help would be appreciated
Thanks
Uwe Brauer
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 8:28 problem with empty column Uwe Brauer
@ 2018-01-05 9:25 ` Loris Bennett
2018-01-05 9:35 ` Eric S Fraga
1 sibling, 0 replies; 9+ messages in thread
From: Loris Bennett @ 2018-01-05 9:25 UTC (permalink / raw)
To: emacs-orgmode
Uwe Brauer <oub@mat.ucm.es> writes:
> Hi
>
> Consider please the following table
>
>
>
> | Name | E1 | E2 | E3 | E4 | Res |
> |--------+----+----+----+----+-----|
> | Entry1 | | | | | NP |
> | Entry2 | 10 | 20 | 30 | 40 | 10 |
>
> #+TBLFM: $6=if("$2" == "nan" , string("NP"),($2+$3+$4+$5)/10);E
>
> The last column contains a string NP if the corresponding cell in the
> second column is empty.
>
> However when I consider
>
> | Name | E1 | E2 | E3 | E4 | Res |
> |--------+----+----+----+----+-----|
> | Entry1 | | | | | NP |
> | Entry2 | 10 | 20 | 30 | 40 | 10 |
> | Entry3 | 10 | | 20 | 30 | nan |
>
> #+TBLFM: $6=if("$2" == "nan" , string("NP"),($2+$3+$4+$5)/10);E
>
> The last row behaves strangely the cell in the second column is *not*
> empty but the cell in the third one is, nevertheless the cell in the
> last column contains the string nan, which I don't understand.
>
> Any help would be appreciated
>
> Thanks
>
> Uwe Brauer
The empty column for Entry3 is in column 3 and not column 2. Try
toggling the Col/Row numbers.
Cheers
Loris
--
This signature is currently under construction.
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 8:28 problem with empty column Uwe Brauer
2018-01-05 9:25 ` Loris Bennett
@ 2018-01-05 9:35 ` Eric S Fraga
2018-01-05 12:44 ` Uwe Brauer
1 sibling, 1 reply; 9+ messages in thread
From: Eric S Fraga @ 2018-01-05 9:35 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 401 bytes --]
On Friday, 5 Jan 2018 at 09:28, Uwe Brauer wrote:
[...]
> The last row behaves strangely the cell in the second column is *not*
> empty but the cell in the third one is, nevertheless the cell in the
> last column contains the string nan, which I don't understand.
But the second column is *not* empty; maybe you meant 3rd column?
--
Eric S Fraga via Emacs 27.0.50, Org release_9.1.6
[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 194 bytes --]
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 9:35 ` Eric S Fraga
@ 2018-01-05 12:44 ` Uwe Brauer
2018-01-05 13:55 ` Michael Brand
2018-01-05 14:46 ` Eric S Fraga
0 siblings, 2 replies; 9+ messages in thread
From: Uwe Brauer @ 2018-01-05 12:44 UTC (permalink / raw)
To: emacs-orgmode
>>> "Eric" == Eric S Fraga <esflists@gmail.com> writes:
> On Friday, 5 Jan 2018 at 09:28, Uwe Brauer wrote: [...]
>> The last row behaves strangely the cell in the second
>> column is *not* empty but the cell in the third one is,
>> nevertheless the cell in the last column contains the
>> string nan, which I don't understand.
> But the second column is *not* empty; maybe you meant 3rd
> column?
What I meant is this.
Take the following simple table
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----| Entry1 | 10 | 30 | 40 | 50 |
|| Entry2 | 10 | 20 | 30 | 40 | | #+TBLFM: $6=($2+$3+$4+$5)/10
C-u C-u C-c C-c
leads to
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----| Entry1 | 10 | 30 | 40 | 50 |
|13 | Entry2 | 10 | 20 | 30 | 40 | 10 | #+TBLFM:
|$6=($2+$3+$4+$5)/10
Now for any new entry I want to have in the 6th column NP if the
entry in the second column (not the whole column) is empty.
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----| Entry1 | 10 | 30 | 40 | 50 |
|13 | Entry2 | 10 | 20 | 30 | 40 | 10 | Entry3 | | | |
|| |
Should lead to
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----| Entry1 | 10 | 30 | 40 | 50 |
|13 | Entry2 | 10 | 20 | 30 | 40 | 10 | Entry3 | | | |
|| NP |
How can I achieve that?
The following works well
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----| Entry1 | | | | |
|NP | Entry2 | 10 | 20 | 30 | 40 | 10 | #+TBLFM: $6=if("$2" ==
|"nan" , string("NP"),($2+$3+$4+$5)/10);E
although the second column is *not* empty, but it is empty for
Entry 1.
But fails if there is a row like this
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----| Entry1 | | | | |
|NP | Entry2 | 10 | 20 | 30 | 40 | 10 | Entry3 | 10 | | 20 |
|30 | nan | #+TBLFM: $6=if("$2" == "nan" ,
|string("NP"),($2+$3+$4+$5)/10);E
Uwe
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 12:44 ` Uwe Brauer
@ 2018-01-05 13:55 ` Michael Brand
2018-01-06 8:43 ` Uwe Brauer
2018-01-05 14:46 ` Eric S Fraga
1 sibling, 1 reply; 9+ messages in thread
From: Michael Brand @ 2018-01-05 13:55 UTC (permalink / raw)
To: Org Mode
Hi Uwe
On Fri, Jan 5, 2018 at 1:44 PM, Uwe Brauer <oub@mat.ucm.es> wrote:
> But fails if there is a row like this
>
> | Name | E1 | E2 | E3 | E4 | Res |
> |--------+----+----+----+----+-----|
> | Entry1 | | | | | NP |
> | Entry2 | 10 | 20 | 30 | 40 | 10 |
> | Entry3 | 10 | | 20 | 30 | nan |
> #+TBLFM: $6=if("$2" == "nan", string("NP"),($2+$3+$4+$5)/10);E
I think you are looking for this:
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----|
| Entry1 | | | | | NP |
| Entry2 | 10 | 20 | 30 | 40 | 10 |
| Entry3 | 10 | | 20 | 30 | NP |
#+TBLFM: $6 = if(typeof(vsum($2..$5)) == 12 , string("NP"),
vsum($2..$5) / 10); E
See the example
if(typeof(vmean($1..$7)) == 12, string(""), vmean($1..$7); E
in the manual.
Michael
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 12:44 ` Uwe Brauer
2018-01-05 13:55 ` Michael Brand
@ 2018-01-05 14:46 ` Eric S Fraga
2018-01-06 8:37 ` Uwe Brauer
1 sibling, 1 reply; 9+ messages in thread
From: Eric S Fraga @ 2018-01-05 14:46 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 381 bytes --]
On Friday, 5 Jan 2018 at 13:44, Uwe Brauer wrote:
> What I meant is this.
I understood what you meant and your code is working properly. You
are checking the second column which is the column labelled E1, not
the one labelled E2 which is actually the third column... change your
if statement to check $3, not $2.
--
Eric S Fraga via Emacs 27.0.50, Org release_9.1.6
[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 194 bytes --]
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 14:46 ` Eric S Fraga
@ 2018-01-06 8:37 ` Uwe Brauer
2018-01-06 11:23 ` Eric S Fraga
0 siblings, 1 reply; 9+ messages in thread
From: Uwe Brauer @ 2018-01-06 8:37 UTC (permalink / raw)
To: emacs-orgmode
>>> "Eric" == Eric S Fraga <esflists@gmail.com> writes:
> On Friday, 5 Jan 2018 at 13:44, Uwe Brauer wrote:
>> What I meant is this.
> I understood what you meant and your code is working properly. You
> are checking the second column which is the column labelled E1, not
> the one labelled E2 which is actually the third column... change your
> if statement to check $3, not $2.
Maybe the format of the table was distorted.
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----|
| Entry1 | | | | | NP |
| Entry2 | 10 | 20 | 30 | 40 | 10 |
| Entry3 | 10 | | 20 | 30 | nan |
#+TBLFM: $6=if("$2" == "nan" , string("NP"),($2+$3+$4+$5)/10);E
The column of reference is E1 per default, which is the second one. The
row labeled Entry1 gives the expected result, NP, since its E1 entry is
empty but in the row labeled Entry3 E1 is not empty, so the second part
of the statement should be applied resulting in 6, but it does not it
results in nan, which I don't understand. The fact that E2 ($3) is empty
should be irrelevant here, but it is not.
On the other hand
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----|
| Entry1 | | | | | NP |
| Entry2 | 10 | 20 | 30 | 40 | 10 |
| Entry3 | 10 | 0 | 20 | 30 | 6 |
#+TBLFM: $6=if("$2" == "nan" , string("NP"),($2+$3+$4+$5)/10);E
Gives the expected result, that is adding 0 to E2 in row Entry3.
I am puzzled
Uwe
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-05 13:55 ` Michael Brand
@ 2018-01-06 8:43 ` Uwe Brauer
0 siblings, 0 replies; 9+ messages in thread
From: Uwe Brauer @ 2018-01-06 8:43 UTC (permalink / raw)
To: emacs-orgmode
>>> "Michael" == Michael Brand <michael.ch.brand@gmail.com> writes:
Hi Michael
> if(typeof(vmean($1..$7)) == 12, string(""), vmean($1..$7); E
This is precisely what I was looking for, thanks a lot. I meanwhile
circumvent the problem by
| Name | E1 | E2 | E3 | E4 | Pre | Res |
|--------+----+----+----+----+-----+-----|
| Entry1 | | | | | 0 | NP |
| Entry2 | 10 | 20 | 30 | 40 | 100 | 10 |
| Entry3 | 10 | | 20 | 30 | 60 | 6 |
#+TBLFM: $6=$2+$3+$4+$5::$7=if("$2" == "nan", string("NP"),$6/10);E
But your solution is much more elegant.
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: problem with empty column
2018-01-06 8:37 ` Uwe Brauer
@ 2018-01-06 11:23 ` Eric S Fraga
0 siblings, 0 replies; 9+ messages in thread
From: Eric S Fraga @ 2018-01-06 11:23 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 811 bytes --]
On Saturday, 6 Jan 2018 at 09:37, Uwe Brauer wrote:
> The column of reference is E1 per default, which is the second one. The
Ah, okay, I understand now. Thanks.
I see that you have been given a solution elsewhere using vmean but
you could also consider adding "N" to the expression (i.e. after
;E). This would make the empty entries be treated as having a 0 value
so your check for nan would no longer work but you could always check
for 0... The following works:
| Name | E1 | E2 | E3 | E4 | Res |
|--------+----+----+----+----+-----|
| Entry1 | | | | | NP |
| Entry2 | 10 | 20 | 30 | 40 | 10 |
| Entry3 | 10 | | 20 | 30 | 6 |
#+TBLFM: $6=if("$2" == "(0)" , string("NP"),($2+$3+$4+$5)/10);EN
HTH,
eric
--
Eric S Fraga via Emacs 27.0.50, Org release_9.1.6
[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 194 bytes --]
^ permalink raw reply [flat|nested] 9+ messages in thread
end of thread, other threads:[~2018-01-06 11:23 UTC | newest]
Thread overview: 9+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-01-05 8:28 problem with empty column Uwe Brauer
2018-01-05 9:25 ` Loris Bennett
2018-01-05 9:35 ` Eric S Fraga
2018-01-05 12:44 ` Uwe Brauer
2018-01-05 13:55 ` Michael Brand
2018-01-06 8:43 ` Uwe Brauer
2018-01-05 14:46 ` Eric S Fraga
2018-01-06 8:37 ` Uwe Brauer
2018-01-06 11:23 ` Eric S Fraga
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).