table: problem with nan and if

table: problem with nan and if

[Uwe Brauer]

Hi

I have the following table 

#+begin_src elisp
#+NAME: test
| Name  | E1 | E2 |  E3 | Result1 | Result2 | Final |
|-------+----+----+-----+---------+---------+-------|
| User1 |    |    |     |         |       8 | nan    |
| User2 |    |    |     |         |         |        |
| User3 |  1 |  0 | 3.5 |     4.5 |     5.8 | 4.8    |
|-------+----+----+-----+---------+---------+-------|
#+TBLFM: $5=if(typeof(vsum($2..$4)) == 12, string(" "),vsum($2..$4));E f-1::$7=if("$6" == "nan", string(" "),0.3*$5+0.6*$6); E f-1
#+end_src

The calculations for User2 and 3 are fine, but for User1, the final
result is a  "nan".

I see the problem is caused by ; E f-1 I don't understand why because

As in 
#+begin_src elisp
| user1 | User2 |     |
|-------+-------+-----|
|       |     3 | nan |
|     0 |     1 | 0.8 |
#+TBLFM: $3=0.2*$1+0.8*$2; E f-1
#+end_src

I am not sure, but should I use something like 

if("$5" == "nan", string(" "),0.6*$6); E;f-1


But how can I combine both ifs, I am puzzled

Any  help is strongly appreciated.


Uwe Brauer 

Re: table: problem with nan and if

[Eric S Fraga]

Uwe,

it might be easier if you explained what it is you want to do (at a
higher level) instead of trying to get us to help to fix what you have
done?

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

Re: table: problem with nan and if

[Uwe Brauer]

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

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

> Uwe,
> it might be easier if you explained what it is you want to do (at a
> higher level) instead of trying to get us to help to fix what you have
> done?

OK fair enough 


The situation is as follows, there are two exams


    1. The result of the first one is calculated as: E1+E2+E3=Result-Ex1

    2. The second one Result-Ex2 (column 6, I have extracted from another table via

#+BEGIN: aggregate :table Ex1 :cols "Name  'Result-Ex1 "
But this is not important here.

What is important is how the columns $5, and $7 are calculated.
If column $2,$3, and $4 are non empty $5 is just the sum of these.

The problem is $7:

    1. it should be the sum of $5 and $6, *even* if $5 is empty,

    2. but it should be empty if $6 is empty.



#+begin_src elisp

#+NAME: test
| Name  | E1 | E2 |  E3 | Result-Ex1 | Result-Ex2 | Final |
|-------+----+----+-----+------------+------------+-------|
| User1 |    |    |     |            |          8 |       |
| User2 |    |    |     |            |            |       |
| User3 |  1 |  0 | 3.5 |        4.5 |        5.8 |   4.8 |
|-------+----+----+-----+------------+------------+-------|
#+end_src



Did I explain this detailed and clear enough?

Uwe 

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

Re: table: problem with nan and if

[Eric S Fraga]

Uwe,

what if you change the E in each equation with N?  You'll get 0 entries
when the calculations involve all empty cells which might not be what
you want, of course.
-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-566-gf0198e
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: table: problem with nan and if

[tbanelwebmin]

About the E format setting, you may look at documentation here:
[[info:org#Formula syntax for Calc]]


Le 22/06/2021 à 09:44, Eric S Fraga a écrit :
> Uwe,
>
> what if you change the E in each equation with N?  You'll get 0 entries
> when the calculations involve all empty cells which might not be what
> you want, of course.

Re: table: problem with nan and if

[Uwe Brauer]

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

>>> "t" == tbanelwebmin  <tbanelwebmin@free.fr> writes:

> About the E format setting, you may look at documentation here:
> [[info:org#Formula syntax for Calc]]



Thanks I tried, but I think the core of the problem is that $7 needs two
ifs with two commands

#+begin_src elisp

Case I $6 empty
#+NAME: test
| Name  | E1 | E2 |  E3 | Result1 | Result2 | Final |
|-------+----+----+-----+---------+---------+-------|
| User1 |    |    |     |         |       8 |   nan |
| User2 |    |    |     |         |         |       |
| User3 |  1 |  0 | 3.5 |     4.5 |     5.8 |   4.8 |
|-------+----+----+-----+---------+---------+-------|
#+TBLFM: $5=if(typeof(vsum($2..$4)) == 12, string(" "),vsum($2..$4));E f-1::$7=if("$6" == "nan", string(" "),0.3*$5+0.6*$6);E f-1

Case II $5 empty
#+NAME: test2
| Name  | E1 | E2 |  E3 | Result1 | Result2 | Final  |
|-------+----+----+-----+---------+---------+--------|
| User1 |    |    |     |         |       8 | #ERROR |
| User2 |    |    |     |         |         | #ERROR |
| User3 |  1 |  0 | 3.5 |     4.5 |     5.8 | #ERROR |
|-------+----+----+-----+---------+---------+--------|
#+TBLFM: $5=if(typeof(vsum($2..$4)) == 12, string(" "),vsum($2..$4));E f-1::$7=if("$5" == "nan", 0.6*$6),0.3*$5+0.6*$6);E f-1

#+end_src

But even the second case does not work, sigh

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

Re: table: problem with nan and if

[Uwe Brauer]

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

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

> Uwe,
> what if you change the E in each equation with N?  You'll get 0 entries
> when the calculations involve all empty cells which might not be what
> you want, of course.

In that case I obtain a 0, which I don't want

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

Re: table: problem with nan and if

[Tim Cross]

Uwe Brauer <oub@mat.ucm.es> writes:

> Hi
>
> I have the following table 
>
> #+begin_src elisp
> #+NAME: test
> | Name  | E1 | E2 |  E3 | Result1 | Result2 | Final |
> |-------+----+----+-----+---------+---------+-------|
> | User1 |    |    |     |         |       8 | nan    |
> | User2 |    |    |     |         |         |        |
> | User3 |  1 |  0 | 3.5 |     4.5 |     5.8 | 4.8    |
> |-------+----+----+-----+---------+---------+-------|
> #+TBLFM: $5=if(typeof(vsum($2..$4)) == 12, string(" "),vsum($2..$4));E f-1::$7=if("$6" == "nan", string(" "),0.3*$5+0.6*$6); E f-1
> #+end_src
>
>
> The calculations for User2 and 3 are fine, but for User1, the final
> result is a  "nan".
>
> I see the problem is caused by ; E f-1 I don't understand why because
>
> As in 
>
> #+begin_src elisp
> | user1 | User2 |     |
> |-------+-------+-----|
> |       |     3 | nan |
> |     0 |     1 | 0.8 |
> #+TBLFM: $3=0.2*$1+0.8*$2; E f-1
> #+end_src
>
> I am not sure, but should I use something like 
>
> if("$5" == "nan", string(" "),0.6*$6); E;f-1
>
>
> But how can I combine both ifs, I am puzzled
>
> Any  help is strongly appreciated.
>
>

I'm not very familiar with calc, but am wondering if the issue is the
'nan'. In many languages, a nan is a 'polluting' variable i.e. once you
have a nan as a form anywhere in your calculation, the result will
always be a nan. Many languages actually have a special function to test
for a nan because it isn't actually a 'value'. Don't know if this is the
case with calc. 

Perhaps an alternative strategy might help. Could you address what is
generating the nan and change that so that it generates something else,
possibly even a blank string and avoid the nan altogether? 

--
Tim Cross

Re: table: problem with nan and if

[Uwe Brauer]

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


> Uwe Brauer <oub@mat.ucm.es> writes:


> I'm not very familiar with calc, but am wondering if the issue is the
> 'nan'. In many languages, a nan is a 'polluting' variable i.e. once you
> have a nan as a form anywhere in your calculation, the result will
> always be a nan. Many languages actually have a special function to test
> for a nan because it isn't actually a 'value'. Don't know if this is the
> case with calc. 

Yeah, when I googled, I found complains about nan using in calc but I
did not really found an working alternative.

> Perhaps an alternative strategy might help. Could you address what is
> generating the nan and change that so that it generates something else,
> possibly even a blank string and avoid the nan altogether? 

I tried nil and other expressions but non really worked, so maybe a calc
guru could clarify?

Regards

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

Re: table: problem with nan and if

[Nick Dokos]

Uwe Brauer <oub@mat.ucm.es> writes:

>> Uwe Brauer <oub@mat.ucm.es> writes:
>
>
>> I'm not very familiar with calc, but am wondering if the issue is the
>> 'nan'. In many languages, a nan is a 'polluting' variable i.e. once you
>> have a nan as a form anywhere in your calculation, the result will
>> always be a nan. Many languages actually have a special function to test
>> for a nan because it isn't actually a 'value'. Don't know if this is the
>> case with calc. 
>
> Yeah, when I googled, I found complains about nan using in calc but I
> did not really found an working alternative.
>
>> Perhaps an alternative strategy might help. Could you address what is
>> generating the nan and change that so that it generates something else,
>> possibly even a blank string and avoid the nan altogether? 
>
> I tried nil and other expressions but non really worked, so maybe a calc
> guru could clarify?
>

Could you write the formulas in lisp instead? You might be able to control
things more easily:

  (info "(org) Formula syntax for Lisp")

-- 
Nick

"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler

Re: table: problem with nan and if

[Uwe Brauer]

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


> Uwe Brauer <oub@mat.ucm.es> writes:

> Could you write the formulas in lisp instead? You might be able to control
> things more easily:

Aha, that I will try. Thanks for pointing it out. So it seems that my
question is not trivial.

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