From mboxrd@z Thu Jan 1 00:00:00 1970 From: Rainer M Krug Subject: Re: Re: org-table formulas with missing values Date: Sat, 5 Mar 2011 12:01:16 +0100 Message-ID: References: <86aahitzny.fsf@googlemail.com> <86ei6utkez.fsf@googlemail.com> <87ipw5prxc.fsf@altern.org> <86vczxbzuy.fsf@googlemail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Return-path: Received: from [140.186.70.92] (port=44669 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PvpEd-0008Jk-Bu for emacs-orgmode@gnu.org; Sat, 05 Mar 2011 06:01:20 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PvpEb-0003EO-Lw for emacs-orgmode@gnu.org; Sat, 05 Mar 2011 06:01:19 -0500 Received: from mail-qy0-f176.google.com ([209.85.216.176]:45020) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PvpEb-0003EJ-Jn for emacs-orgmode@gnu.org; Sat, 05 Mar 2011 06:01:17 -0500 Received: by qyk30 with SMTP id 30so2753133qyk.0 for ; Sat, 05 Mar 2011 03:01:16 -0800 (PST) In-Reply-To: <86vczxbzuy.fsf@googlemail.com> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Thorsten Cc: emacs-orgmode@gnu.org On Sat, Mar 5, 2011 at 11:55 AM, Thorsten wrote: > Bastien writes: > >> Hi Thorsten, >> >> Thorsten writes: >> >>> Ok, trial and error suggests that missing values in numeric columns can >>> be represented as 0 in formulas, in string columns as "". Is there >>> something like NaN in calc/org-table? >> >> I don't know what is NaN. =A0Can you give an example of what you're tryi= ng >> to achieve? > > NaN stands for NotaNumber and is usually used in statistic programs to > denote missing numerical values NaN ia, as you state, NotANumber, but it is NOT a missing value, for which the abbreviation is NA (Not Vavailable). An example for NaN would be 1/0 - the value is there, but it is not a number. And, consequently, NaN and NA can be treated separately. In general, if you enter values, you use NA, as you usually do not enter values which are not a number... > (that deserve special treatment, since > including them in the calculations as 0 values would not be wise due to > introduced bias or confusion with true measurements of a value=3D0). True. > > Fot example: > > | day | tel- calls | =A0 =A0 =A0 =A0 =A0change (%) | > |-----+------------+---------------------| > | =A0 1 | =A0 =A0 =A0 =A0 =A07 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 | > | =A0 2 | =A0 =A0 =A0 =A0 =A02 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -71 | > | =A0 3 | =A0 =A0 =A0 =A0 =A03 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A050 | > | =A0 4 | =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 = | > | =A0 5 | =A0 =A0 =A0 =A0 =A02 | round((2/0) 100, 0) | > | =A0 6 | =A0 =A0 =A0 =A0 =A00 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 | > | =A0 7 | =A0 =A0 =A0 =A0 =A03 | round((3/0) 100, 0) | > > #+TBLFM: $3=3Dif(@$-1=3D0,0,round((((@$-1-@-1$-1) / @-1$-1) * 100), > =A00)::@2$3=3D@2$2-@2$2 > > > Here one measurement (day 4) was missed, one day had 0 tel-calls (day > 6). > The change (%) is : > ((calls day x) - (calls day x-1)) / (calls day x-1) * 100. > > The column-formula treats measurment and zero value the same, although > it would be better to replace the missing value with the average (but > how to adress it in the formula if not with 0 ?), and it treats the zero > measurement like a missing value here. Even if there would be no zero > value here, the missing value would cause a 'divide by zero' error. > > I could write 'nan' instead of leaving a blank, and use the string in > the formula, but this causes errors. > > But calc does know the concept of nan: "The variables `inf', `uinf', and > `nan' stand for infinite or indeterminate values. =A0It's best not to use > them as regular variables, since Calc uses special algebraic rules when > it manipulates them. =A0Calc displays a warning message if you store a > value into any of these special variables." > > I'm not sure how to use that nan variable in tables and formulas - it > seems not to be recognized. > > Thorsten > > > --=20 NEW GERMAN FAX NUMBER!!! Rainer M. Krug, PhD (Conservation Ecology, SUN), MSc (Conservation Biology, UCT), Dipl. Phys. (Germany) Centre of Excellence for Invasion Biology Natural Sciences Building Office Suite 2039 Stellenbosch University Main Campus, Merriman Avenue Stellenbosch South Africa Cell:=A0 =A0 =A0 =A0 =A0=A0 +27 - (0)83 9479 042 Fax:=A0 =A0 =A0 =A0 =A0 =A0 +27 - (0)86 516 2782 Fax:=A0 =A0 =A0 =A0 =A0 =A0 +49 - (0)321 2125 2244 email:=A0 =A0 =A0 =A0 =A0 Rainer@krugs.de Skype:=A0 =A0 =A0 =A0 =A0 RMkrug Google:=A0 =A0 =A0 =A0=A0 R.M.Krug@gmail.com