Re: Distinguish between blank and zero in org-mode spreadsheet

[Nick Dokos <nicholas.dokos@hp.com>]

Bob Newell <bobnewell@bobnewell.net> 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