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