From mboxrd@z Thu Jan 1 00:00:00 1970 From: Nick Dokos Subject: Re: Distinguish between blank and zero in org-mode spreadsheet Date: Tue, 11 Dec 2012 01:10:38 -0500 Message-ID: <5596.1355206238@alphaville> References: <87r4mxv7l8.fsf@googlemail.com> Reply-To: nicholas.dokos@hp.com Return-path: Received: from eggs.gnu.org ([208.118.235.92]:58775) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TiJ3D-0003pE-GD for emacs-orgmode@gnu.org; Tue, 11 Dec 2012 01:10:44 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1TiJ3A-0002v6-Pp for emacs-orgmode@gnu.org; Tue, 11 Dec 2012 01:10:43 -0500 Received: from g5t0006.atlanta.hp.com ([15.192.0.43]:20801) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TiJ3A-0002us-LD for emacs-orgmode@gnu.org; Tue, 11 Dec 2012 01:10:40 -0500 In-Reply-To: Message from Bob Newell of "Tue, 11 Dec 2012 04:50:50 GMT." List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Bob Newell Cc: emacs-orgmode@gnu.org Bob Newell wrote: > I'm making it work, and using "L" rather than "S" turns out to be better, as in > this revision of my example above: > > $9 = '(if (eq "$2" "") "" (* @2$8 $1));L > > But when I want different actions when there is an explicit number (including 0) > vs. a blank cell, and if my action is at all complex, I end up with one monster > like this: > > $3 = '(if (eq "$2" "") "" (if (< $2 @2$8) 0 (calcFunc-max 0 (calcFunc-ilog (/ $2 > @2$8) 2))));L > > and it just seems that "there oughta be a way" to do this with calc and outside > of elisp. I've tried all sorts of calc things without success to date. > I don't know of a calc way to do it, but I'm no calc expert. > (This gets even worse when you work with ranges and need to do operations on the > cells in those ranges.) > > I guess the upside is that with elisp you can eventually do almost anything that > comes to mind, if you have enough patience. > Yes indeed - but using babel, you can also organize your file so all the complexity is encapsulated: --8<---------------cut here---------------start------------->8--- * helpers #+BEGIN_SRC elisp (defun some-func-wrapper (x y) (cond ((and (stringp x) (stringp y)) (if (or (string= x "") (string= y "")) "" (some-func (string-to-number x) (string-to-number y)))))) (defun some-func (x y) (* x y)) (defun sum-of-squares-wrapper (&rest x) (sum-of-squares (mapcar 'string-to-number x))) (defun sum-of-squares (l) (apply '+ (mapcar (lambda (x) (* x x)) l))) #+END_SRC #+RESULTS: : sum-of-squares * table with empty cells | a | b | c | d | e | |---+---+----+---+----| | 1 | 1 | 1 | 6 | 14 | | 2 | 3 | 6 | 5 | 13 | | | | | 3 | 9 | | 3 | 4 | 12 | 3 | 9 | #+TBLFM: $3 = '(some-func-wrapper $1 $2) :: $4 = vsum(@0$1..@>$1) :: $5 = '(sum-of-squares-wrapper @0$1..@>$1) --8<---------------cut here---------------end--------------->8--- C-c C-c on the code block to define the functions and C-c C-c on the table formula to recalculate the table. C-c ' on the code block allows you to edit it conveniently. My examples do not try to duplicate exactly any of your examples, but I hope they can be generalized (and fairly easily too). Nick