From mboxrd@z Thu Jan 1 00:00:00 1970 From: Thorsten Subject: Re: Re: org-table formulas with missing values Date: Sun, 06 Mar 2011 23:44:59 +0100 Message-ID: <86sjuzdg2c.fsf@googlemail.com> 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=utf-8 Content-Transfer-Encoding: 8bit Return-path: Received: from [140.186.70.92] (port=42772 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PwMhP-00019C-1I for emacs-orgmode@gnu.org; Sun, 06 Mar 2011 17:45:16 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PwMhN-0007du-HO for emacs-orgmode@gnu.org; Sun, 06 Mar 2011 17:45:14 -0500 Received: from lo.gmane.org ([80.91.229.12]:59207) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PwMhN-0007dk-5d for emacs-orgmode@gnu.org; Sun, 06 Mar 2011 17:45:13 -0500 Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1PwMhJ-0005sT-No for emacs-orgmode@gnu.org; Sun, 06 Mar 2011 23:45:09 +0100 Received: from e178116196.adsl.alicedsl.de ([85.178.116.196]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 06 Mar 2011 23:45:09 +0100 Received: from gruenderteam.berlin by e178116196.adsl.alicedsl.de with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 06 Mar 2011 23:45:09 +0100 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: emacs-orgmode@gnu.org Rainer M Krug writes: > 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.  Can you give an example of what you're trying >>> 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... It seems there is no special variable 'na' in calc, and anyway, 'nan' is not recognized either, but calc-info agrees with you. nil does the job - but somehow interferes with the else-part of the formula, that is not calculated anymore: | day | tel- calls | change (%) | |-----+------------+-------------------------------------------------| | 1 | 7 | 0 | | 2 | 2 | 2 = nil ? nil : round(((2 - 7) / 7) 100, 0) | | 3 | 3 | 3 = nil ? nil : round(((3 - 2) / 2) 100, 0) | | 4 | nil | nil | | 5 | 2 | 2 = nil ? nil : round(((2 - nil) / nil) 100, 0) | | 6 | 0 | 0 = nil ? nil : round(((0 - 2) / 2) 100, 0) | | 7 | 3 | 3 = nil ? nil : round(((3 - 0) / 0) 100, 0) | #+TBLFM: $3=if(@$-1=nil,nil,round((((@$-1-@-1$-1) / @-1$-1) * 100), 0)::@2$3=@2$2-@2$2 But even if nil would work, my basic question remains: if I have for example a dynamic report were one code-block in the org file retrieves new data frequently and stores them in a table, which has some formula applied to it, and 0 values as well as missing values are possible, how can I address the empty cells in the formula without confusion with regards to the cells with 0 values? Thorsten