Distinguish between blank and zero in org-mode spreadsheet

Distinguish between blank and zero in org-mode spreadsheet

[Bob Newell]

[-- Attachment #1: Type: text/plain, Size: 942 bytes --]

Can't find out about this anywhere.  My apologies if I missed something
simple or something already posted.

I want a calculation to appear in column 9 if and only if column 2 is
non-blank. If there is an explicit 0 in column 2, I want the calculation to
appear in column 9. But if column 2 is blank, I also want column 9 to be
blank.

I made it work with the following elisp example:

$9 = '(if (eq $2 "") "" (* (string-to-number @2$8) (string-to-number $1)));S

But the question is, can it be made to work with a calc formula, which
would be possibly a little more straightforward? All of the other columns
have the same requirement, and some of the formulas are quite long, so
doing many, many string-to-number functions makes things very cluttered.

The problem obviously revolves around a blank cell being interpreted as
zero. That's all well and good, and quite correct, but makes distinguishing
blank and explicit zero more difficult.

[-- Attachment #2: Type: text/html, Size: 1105 bytes --]

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

[Thorsten Jolitz]

Bob Newell <bobnewell@bobnewell.net> writes:

> The problem obviously revolves around a blank cell being interpreted
> as zero. That's all well and good, and quite correct, but makes
> distinguishing blank and explicit zero more difficult.

I don't think thats "well and good, and quite correct", otherwise all
the statistics packages wouldn't have the notion of 'Na' (not available)
or 'NaN' (Not a Number). I was bothered by the same problem like you,
and its makes life quite complicated when trying to write formulas. 

AFAIL calc actually has the notion of NaN, but it doesn't work in Org
tables, at least when I tried (quite some time ago). 
This would be a great improvement, if table formulas actually could
reference 'empty cells'. 

-- 
cheers,
Thorsten

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

[Bob Newell]

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. 

(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.

 

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

[Nick Dokos]

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

     

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

[Michael Brand]

Hi all

I would also like if some interest could be spent in this (see subject)
area. Let me give some examples as an overview, grouped as “check if
empty”, “range” and “single fields”. The first is the only one
directly related to the OP question. But the others still match the
subject perfectly. Most of the examples include my suggestions of how
I expect them to work, hopefully ending up in a regression test at
some time.

- check if empty: $3 results should be as $4 or even better as $5 if
  not too difficult. I don’t know of any Calc formula that does this
  currently, so I took the one I would prefer to use.
  | 1 |    0 | 1 =  && 0 =  ?  : 1 + 0       |        1 |      1 |
  | 1 |      | 1 =  && 0 =  ?  : 1 + 0       |          |        |
  | 1 | text | 1 =  && text =  ?  : 1 + text | 1 + text | #ERROR |
  #+TBLFM: $3 = if($1 = string("") && $2 = string(""), string(""), $1 + $2)

- input from range as vsum($1..$2):
  1) empty -> 0, non-number -> 0: The format specifier is documented
     as “N: interpret all fields as numbers, use 0 for non-numbers”.
     Works as expected.
     | 1 |    0 | 1 |
     | 1 |      | 1 |
     | 1 | text | 1 |
     #+TBLFM: $3 = vsum($1..$2); N

  2) empty -> 0, keep other non-numbers: Without “N” I would prefer $3
     results to be as $4 if not too difficult.
     | 1 |    0 |        1 |      1 |
     | 1 |      |        1 |      1 |
     | 1 | text | 1 + text | #ERROR |
     #+TBLFM: $3 = vsum($1..$2)

  3) numbers only: The format specifier is documented as “E: keep
     empty fields in ranges”. I would prefer $3 results to be as $4 if
     not too difficult.
     | 1 |    0 | 1        | 1      |
     | 1 |      | #ERROR   | #ERROR |
     | 1 | text | 1 + text | #ERROR |
     #+TBLFM: $3 = vsum($1..$2); E

- input from single fields as $1 + $2:
  1) empty -> 0, non-number -> 0: Works as expected.
     | 1 |    0 | 1 |
     | 1 |      | 1 |
     | 1 | text | 1 |
     #+TBLFM: $3 = $1 + $2; N

  2) empty -> 0, keep other non-numbers: Without “N” I would prefer $3
     results to be as $4 if not too difficult.
     | 1 |    0 |        1 |      1 |
     | 1 |      |        1 |      1 |
     | 1 | text | 1 + text | #ERROR |
     #+TBLFM: $3 = $1 + $2

  3) numbers only: I suggest to extend the currently documented
     “E: keep empty fields in ranges” to “E: keep empty fields” or
     clearer “E: keep empty fields as non-numbers” and then would
     prefer $3 results to be as $4 or if not too difficult even better
     as $5.
     | 1 |    0 |        1 |        1 | 1      |
     | 1 |      |        1 |          | #ERROR |
     | 1 | text | 1 + text | 1 + text | #ERROR |
     #+TBLFM: $3 = $1 + $2; E

Minor issue: I wonder what the entry “vmean($2..$7);EN : Same, but
treat empty fields as 0” in the manual means or is useful for.

Michael

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

[Michael Brand]

Hi all

On Tue, Dec 11, 2012 at 4:14 PM, Michael Brand
<michael.ch.brand@gmail.com> wrote:
> I would also like if some interest could be spent in this (see subject)
> area.

Ok, so I thought to try myself this time. Now it looks as I am on a
good way with a solution. With quite some ERT for this and some more
of basic formulas.

Michael

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

[Bastien]

Hi Michael,

Michael Brand <michael.ch.brand@gmail.com> writes:

> Ok, so I thought to try myself this time. Now it looks as I am on a
> good way with a solution. With quite some ERT for this and some more
> of basic formulas.

Thanks for working on this, if there is a clean solution, I'm willing
to add it for Org 8.0.

Best,

-- 
 Bastien

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

[Michael Brand]

[-- Attachment #1: Type: text/plain, Size: 2086 bytes --]

Hi all

On Sat, Dec 15, 2012 at 10:23 AM, Bastien <bzg@altern.org> wrote:
> Michael Brand <michael.ch.brand@gmail.com> writes:
>> Ok, so I thought to try myself this time. Now it looks as I am on a
>> good way with a solution. With quite some ERT for this and some more
>> of basic formulas.
>
> Thanks for working on this, if there is a clean solution, I'm willing
> to add it for Org 8.0.

Patch 6 of the attached patches makes it possible to write
spreadsheet Calc formulas that check for empty fields: To sum the
first two columns unless one or both empty:
$3 = if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E

The other patches:

- patch 1: testing/README: add howto run ERT partially

- patch 2: Add ERT for table alignment within Org buffer

  The new function org-test-table-target-expect is to simplify writing
  spreadsheet ERT by providing just a target table to apply the formula
  to, the expected result table and the table formula with optionally
  additional variants. The variants typically are to check the same
  result with a Lisp formula and a Calc formula. test-org-table.el is
  also a howto example collection as a user documentation.

- patch 3: Extend org-table-number-regexp

- patch 4: Add ERTs for basic spreadsheet functionality

  My plan is to add spreadsheet examples only to Org ERT
  (test-org-table.el) from now on and someday to move those I already
  made on Worg.

- patch 5: org-table.el: Fix range len bugs and inconsistencies

  The range len bugs may lead to wrong calculations for range references
  with empty fields when the range len is relevant.  Affects typically
  Calc vmean on simple range and without format specifier EN.  Also
  Lisp with e. g. `length' on simple range or with L.

Note: Org ERT passes after every single patch to document the behavior
of each patch.

After the review is complete please make sure that “make check” passes
before applying the patches and also before git push. I hope I got it
right with the require to resolve the dependencies.

Michael

[-- Attachment #2: 0001-testing-README-add-howto-run-ERT-partially.patch.txt --]
[-- Type: text/plain, Size: 2450 bytes --]

From 2e2479a894076108a34dbaacc46658e3eb5ec16c Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 14:58:24 +0100
Subject: [PATCH 1/6] testing/README: add howto run ERT partially

* testing/README (Interactive testing from within Emacs): Add a
description how to run a single ERT or all tests of a single test
file.
---
 testing/README |   33 ++++++++++++++++++++++++++++++++-
 1 files changed, 32 insertions(+), 1 deletions(-)

diff --git a/testing/README b/testing/README
index a81efa0..8ce7a19 100644
--- a/testing/README
+++ b/testing/README
@@ -43,11 +43,42 @@ load and run the test suite with the following commands.
      (require 'org-test)
    #+END_SRC
 
-2) Then run the test suite.
+2) Then run the test suite,
    #+BEGIN_SRC emacs-lisp
      (org-test-run-all-tests)
    #+END_SRC
 
+   or when a test fails run it interactively and investigate the
+   problem in the ERT results buffer.
+
+   How to run one test:
+   Use this as a demo example of a failing test
+   #+BEGIN_SRC emacs-lisp
+     (ert-deftest test-org/org-link-escape-ascii-character-demo-of-fail ()
+       (should (string= "%5B"  ;; expected is right
+                        (org-link-escape "[")))
+       (should (string= "%5C"  ;; expected is wrong, "%5D" would be right
+                        (org-link-escape "]"))))
+   #+END_SRC
+   or evaluate the ert-deftest form of the test you want to run.  Then
+   "M-x ert RET test-org/org-link-escape-ascii-character-demo-of-fail RET"
+   When not visible yet switch to the ERT results buffer named
+   "\*ert\*".  When a test failed the ERT results buffer shows the
+   details of the first "should" that failed.  See
+   (info "(ert)Running Tests Interactively") on how to re-run, start
+   the debugger etc.
+
+   How to run all tests of a single test file:
+   "M-x ert-delete-all-tests RET", confirm.  Open the file
+   ./lisp/test-*.el, "M-x eval-buffer RET", "M-x ert RET t RET"
+
+   Consider to set pp-escape-newlines nil before running the test when
+   looking at "should" in the ERT results buffer.  Especially when
+   using "l" to look at passed test results and possibly missing an
+   appropriate setting of pp-escape-newlines made only temporarily for
+   the running time of the test as e. g. tests using
+   org-test-table-target-expect-tblfm do.
+
 * Troubleshooting
 
 - If the value of the =org-babel-no-eval-on-ctrl-c-ctrl-c= is non-nil
-- 
1.7.1


[-- Attachment #3: 0002-Add-ERT-for-table-alignment-within-Org-buffer.patch.txt --]
[-- Type: text/plain, Size: 4572 bytes --]

From 14c6cc72fd2ee408046d4830c436ae0becfe8144 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 14:59:17 +0100
Subject: [PATCH 2/6] Add ERT for table alignment within Org buffer

* testing/lisp/test-org-table.el (test-org-table/align): New
ert-deftest for table alignment within Org buffer.
* testing/org-test.el (org-test-table-target-expect): New defun.

The new function org-test-table-target-expect is to simplify writing
spreadsheet ERT by providing just a target table to apply the formula
to, the expected result table and the table formula with optionally
additional variants.  The variants typically are to check the same
result with a Lisp formula and a Calc formula.  test-org-table.el is
also a howto example collection as a user documentation.
---
 testing/lisp/test-org-table.el |   14 ++++++++++
 testing/org-test.el            |   52 ++++++++++++++++++++++++++++++++++++++++
 2 files changed, 66 insertions(+), 0 deletions(-)

diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 8b59114..4c899a8 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -23,6 +23,20 @@
 ;; Template test file for Org-mode tests
 
 ;;; Code:
+
+(ert-deftest test-org-table/align ()
+  "Align columns within Org buffer, depends on `org-table-number-regexp'."
+  (org-test-table-target-expect "
+| 0  |  0 |    0 |       0 |       0 |           0 |       0 |    0 |
+| ab | 12 | 12.2 | 2.4e-08 | 2x10^12 | 4.034+-0.02 | 2.7(10) | >3.5 |
+| ab | ab |   ab |      ab |      ab |          ab |      ab |   ab |
+")
+  (org-test-table-target-expect "
+| 0          | 0           |   0 | 0    | 0    | 0   |
+| <-0x0ab.cf | >-36#0vw.yz | nan | uinf | -inf | inf |
+| ab         | ab          |  ab | ab   | ab   | ab  |
+"))
+
 (ert-deftest test-org-table/org-table-convert-refs-to-an/1 ()
   "Simple reference @1$1."
   (should
diff --git a/testing/org-test.el b/testing/org-test.el
index 8270be0..56bcb10 100644
--- a/testing/org-test.el
+++ b/testing/org-test.el
@@ -220,6 +220,58 @@ otherwise place the point at the beginning of the inserted text."
        ,results)))
 (def-edebug-spec org-test-with-temp-text-in-file (form body))
 
+(defun org-test-table-target-expect (target &optional expect laps
+&rest tblfm)
+  "For all TBLFM: Apply the formula to TARGET, compare EXPECT with result.
+Either LAPS and TBLFM are nil and the table will only be aligned
+or LAPS is the count of recalculations that should be made on
+each TBLFM.  To save ERT run time keep LAPS as low as possible to
+get the table stable.  Anyhow, if LAPS is 'iterate then iterate,
+but this will run one recalculation longer.  When EXPECT is nil
+it will be set to TARGET.
+
+If running a test interactively in ERT is not enough and you need
+to examine the target table with e. g. the Org formula debugger
+or an Emacs Lisp debugger (e. g. with point in a data field and
+calling the instrumented `org-table-eval-formula') then copy and
+paste the table with formula from the ERT results buffer or
+temporarily substitute the `org-test-with-temp-text' of this
+function with `org-test-with-temp-text-in-file'.
+
+Consider setting `pp-escape-newlines' to nil manually."
+  (require 'pp)
+  (let ((back pp-escape-newlines) (current-tblfm))
+    (unless tblfm
+      (should-not laps)
+      (push "" tblfm))  ; Dummy formula.
+    (unless expect (setq expect target))
+    (while (setq current-tblfm (pop tblfm))
+      (org-test-with-temp-text (concat target current-tblfm)
+	;; Search table, stop ERT at end of buffer if not found.
+	(while (not (org-at-table-p))
+	  (should (eq 0 (forward-line))))
+	(when laps
+	  (if (and (symbolp laps) (eq laps 'iterate))
+	      (should (org-table-recalculate 'iterate t))
+	    (should (integerp laps))
+	    (should (< 0 laps))
+	    (let ((cnt laps))
+	      (while (< 0 cnt)
+		(should (org-table-recalculate 'all t))
+		(setq cnt (1- cnt))))))
+	(org-table-align)
+	(setq pp-escape-newlines nil)
+	;; Declutter the ERT results buffer by giving only variables
+	;; and not directly the forms to `should'.
+	(let ((expect (concat expect current-tblfm))
+	      (result (buffer-substring-no-properties
+		       (point-min) (point-max))))
+	  (should (equal expect result)))
+	;; If `should' passed then set back `pp-escape-newlines' here,
+	;; else leave it nil as a side effect to see the failed table
+	;; on multiple lines in the ERT results buffer.
+	(setq pp-escape-newlines back)))))
+
 \f
 ;;; Navigation Functions
 (when (featurep 'jump)
-- 
1.7.1


[-- Attachment #4: 0003-Extend-org-table-number-regexp.patch.txt --]
[-- Type: text/plain, Size: 2994 bytes --]

From 2fd7ddf550bc2f4ad7e9894e49bf6bae50485924 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:00:03 +0100
Subject: [PATCH 3/6] Extend org-table-number-regexp

* lisp/org-table.el (org-table-number-regexp): Extend 0x hex to
fixed-point number, add <radix>#<number>, add Calc infinite numbers
uinf, -inf and inf.
* testing/lisp/test-org-table.el (test-org-table/align): Adapt
alignment.
---
 lisp/org-table.el              |   10 +++++-----
 testing/lisp/test-org-table.el |    4 ++--
 2 files changed, 7 insertions(+), 7 deletions(-)

diff --git a/lisp/org-table.el b/lisp/org-table.el
index 509a1ef..6a9d0b4 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -112,7 +112,7 @@ table, obtained by prompting the user."
   :type 'string)
 
 (defcustom org-table-number-regexp
-  "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%:]*\\|\\(0[xX]\\)[0-9a-fA-F]+\\|nan\\)$"
+  "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%:]*\\|[<>]?[-+]?0[xX][0-9a-fA-F.]+\\|[<>]?[-+]?[0-9]+#[0-9a-zA-Z.]+\\|nan\\|[-+u]?inf\\)$"
   "Regular expression for recognizing numbers in table columns.
 If a table column contains mostly numbers, it will be aligned to the
 right.  If not, it will be aligned to the left.
@@ -136,10 +136,10 @@ Other options offered by the customize interface are more restrictive."
 		 "^[-+]?\\([0-9]*\\.[0-9]+\\|[0-9]+\\.?[0-9]*\\)$")
 	  (const :tag "Exponential, Floating point, Integer"
 		 "^[-+]?[0-9.]+\\([eEdD][-+0-9]+\\)?$")
-	  (const :tag "Very General Number-Like, including hex"
-		 "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%]*\\|\\(0[xX]\\)[0-9a-fA-F]+\\|nan\\)$")
-	  (const :tag "Very General Number-Like, including hex, allows comma as decimal mark"
-		 "^\\([<>]?[-+^.,0-9]*[0-9][-+^.0-9eEdDx()%]*\\|\\(0[xX]\\)[0-9a-fA-F]+\\|nan\\)$")
+	  (const :tag "Very General Number-Like, including hex and Calc radix"
+		 "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%]*\\|[<>]?[-+]?0[xX][0-9a-fA-F.]+\\|[<>]?[-+]?[0-9]+#[0-9a-zA-Z.]+\\|nan\\|[-+u]?inf\\)$")
+	  (const :tag "Very General Number-Like, including hex and Calc radix, allows comma as decimal mark"
+		 "^\\([<>]?[-+^.,0-9]*[0-9][-+^.0-9eEdDx()%]*\\|[<>]?[-+]?0[xX][0-9a-fA-F.]+\\|[<>]?[-+]?[0-9]+#[0-9a-zA-Z.]+\\|nan\\|[-+u]?inf\\)$")
 	  (string :tag "Regexp:")))
 
 (defcustom org-table-number-fraction 0.5
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 4c899a8..89704ff 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -32,9 +32,9 @@
 | ab | ab |   ab |      ab |      ab |          ab |      ab |   ab |
 ")
   (org-test-table-target-expect "
-| 0          | 0           |   0 | 0    | 0    | 0   |
+|          0 |           0 |   0 |    0 |    0 |   0 |
 | <-0x0ab.cf | >-36#0vw.yz | nan | uinf | -inf | inf |
-| ab         | ab          |  ab | ab   | ab   | ab  |
+|         ab |          ab |  ab |   ab |   ab |  ab |
 "))
 
 (ert-deftest test-org-table/org-table-convert-refs-to-an/1 ()
-- 
1.7.1


[-- Attachment #5: 0004-Add-ERTs-for-basic-spreadsheet-functionality.patch.txt --]
[-- Type: text/plain, Size: 26306 bytes --]

From 42a845053a8d205ed24f83e14f973118ad8c3fe5 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:00:54 +0100
Subject: [PATCH 4/6] Add ERTs for basic spreadsheet functionality

* doc/org.texi (Formula syntax for Calc): Extend explanations of
format specifiers.
* testing/lisp/test-org-table.el: Various new ert-deftest, mark with
TODO what needs to be fixed.
---
 doc/org.texi                   |   11 +-
 testing/lisp/test-org-table.el |  605 ++++++++++++++++++++++++++++++++++++++--
 2 files changed, 587 insertions(+), 29 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index b160517..0f6297d 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2616,9 +2616,12 @@ n3 s3 e2 f4   @r{Normal, scientific, engineering, or fixed}
               @r{long as the Calc calculation precision is greater.}
 D R           @r{angle modes: degrees, radians}
 F S           @r{fraction and symbolic modes}
-N             @r{interpret all fields as numbers, use 0 for non-numbers}
-E             @r{keep empty fields in ranges}
-L             @r{literal}
+E             @r{keep empty fields in ranges; else suppress empty fields in}
+              @r{range references and use 0 for empty field references, see}
+              @r{also the notes for `Range references' in @pxref{References}}
+N             @r{interpret all fields as numbers, use 0 for non-numbers;}
+              @r{N has higher precedence than E (for the value of the field)}
+L             @r{literal, for Lisp formulas only}
 @end example
 
 @noindent
@@ -2642,7 +2645,7 @@ $0;%.1f              @r{Reformat current cell to 1 decimal}
 $c/$1/$cm            @r{Hz -> cm conversion, using @file{constants.el}}
 tan($1);Dp3s1        @r{Compute in degrees, precision 3, display SCI 1}
 sin($1);Dp3%.1e      @r{Same, but use printf specifier for display}
-vmean($2..$7)        @r{Compute column range mean, using vector function}
+vmean($2..$7)        @r{Compute column range mean, suppress empty fields}
 vmean($2..$7);EN     @r{Same, but treat empty fields as 0}
 taylor($3,x=7,2)     @r{Taylor series of $3, at x=7, second degree}
 @end example
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 89704ff..68949d9 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -1,7 +1,7 @@
 ;;; test-org-table.el --- tests for org-table.el
 
 ;; Copyright (c)  David Maus
-;; Authors: David Maus
+;; Authors: David Maus, Michael Brand
 
 ;; This file is not part of GNU Emacs.
 
@@ -20,10 +20,141 @@
 
 ;;;; Comments:
 
-;; Template test file for Org-mode tests
+;; Template test file for Org-mode tests.  First the tests that are
+;; also a howto example collection as a user documentation, more or
+;; less all those using `org-test-table-target-expect'.  Then the
+;; internal and more abstract tests.  See also the doc string of
+;; `org-test-table-target-expect'.
 
 ;;; Code:
 
+(require 'org-table)  ; `org-table-make-reference'
+
+(ert-deftest test-org-table/simple-formula/no-grouping/no-title-row ()
+  "Simple sum without grouping rows, without title row."
+  (org-test-table-target-expect
+   "
+|       2 |
+|       4 |
+|       8 |
+| replace |
+"
+   "
+|  2 |
+|  4 |
+|  8 |
+| 14 |
+"
+   1
+   ;; Calc formula
+   "#+TBLFM: @>$1 = vsum(@<..@>>)"
+   ;; Lisp formula
+   "#+TBLFM: @>$1 = '(+ @<..@>>); N"))
+
+(ert-deftest test-org-table/simple-formula/no-grouping/with-title-row ()
+  "Simple sum without grouping rows, with title row."
+  (org-test-table-target-expect
+   "
+|     foo |
+|---------|
+|       2 |
+|       4 |
+|       8 |
+| replace |
+"
+   "
+| foo |
+|-----|
+|   2 |
+|   4 |
+|   8 |
+|  14 |
+"
+   1
+   ;; Calc formula
+   "#+TBLFM: @>$1 = vsum(@I..@>>)"
+   ;; Lisp formula
+   "#+TBLFM: @>$1 = '(+ @I..@>>); N"))
+
+(ert-deftest test-org-table/simple-formula/with-grouping/no-title-row ()
+  "Simple sum with grouping rows, how not to do."
+  ;; The first example has a problem, see the second example in this
+  ;; ert-deftest.
+  (org-test-table-target-expect
+   "
+|       2 |
+|       4 |
+|       8 |
+|---------|
+| replace |
+"
+   "
+|  2 |
+|  4 |
+|  8 |
+|----|
+| 14 |
+"
+   1
+   ;; Calc formula
+   "#+TBLFM: $1 = vsum(@<..@>>)"
+   ;; Lisp formula
+   "#+TBLFM: $1 = '(+ @<..@>>); N")
+
+  ;; The problem is that the first three rows with the summands are
+  ;; considered the header and therefore column formulas are not
+  ;; applied on them as shown below.  Also export behaves unexpected.
+  ;; See next ert-deftest how to group rows right.
+  (org-test-table-target-expect
+   "
+|       2 | replace |
+|       4 | replace |
+|       8 | replace |
+|---------+---------|
+| replace | replace |
+"
+   "
+|  2 | replace |
+|  4 | replace |
+|  8 | replace |
+|----+---------|
+| 14 | 28      |
+"
+   2
+   ;; Calc formula
+   "#+TBLFM: @>$1 = vsum(@<..@>>) :: $2 = 2 * $1"
+   ;; Lisp formula
+   "#+TBLFM: @>$1 = '(+ @<..@>>); N :: $2 = '(* 2 $1); N"))
+
+(ert-deftest test-org-table/simple-formula/with-grouping/with-title-row ()
+  "Simple sum with grouping rows, how to do it right."
+  ;; Always add a top row with the column names separated by hline to
+  ;; get the desired header when you want to group rows.
+  (org-test-table-target-expect
+   "
+|     foo | bar     |
+|---------+---------|
+|       2 | replace |
+|       4 | replace |
+|       8 | replace |
+|---------+---------|
+| replace | replace |
+"
+   "
+| foo | bar |
+|-----+-----|
+|   2 |   4 |
+|   4 |   8 |
+|   8 |  16 |
+|-----+-----|
+|  14 |  28 |
+"
+   2
+   ;; Calc formula
+   "#+TBLFM: @>$1 = vsum(@I..@>>) :: $2 = 2 * $1"
+   ;; Lisp formula
+   "#+TBLFM: @>$1 = '(+ @I..@>>); N :: $2 = '(* 2 $1); N"))
+
 (ert-deftest test-org-table/align ()
   "Align columns within Org buffer, depends on `org-table-number-regexp'."
   (org-test-table-target-expect "
@@ -37,6 +168,453 @@
 |         ab |          ab |  ab |   ab |   ab |  ab |
 "))
 
+(defconst references/target-normal "
+| 0 | 1 | replace | replace | replace | replace | replace | replace |
+| z | 1 | replace | replace | replace | replace | replace | replace |
+|   | 1 | replace | replace | replace | replace | replace | replace |
+|   |   | replace | replace | replace | replace | replace | replace |
+"
+  "Normal numbers and non-numbers for Lisp and Calc formula.")
+
+(defconst references/target-special "
+|  nan | 1 | replace | replace | replace | replace | replace | replace |
+| uinf | 1 | replace | replace | replace | replace | replace | replace |
+| -inf | 1 | replace | replace | replace | replace | replace | replace |
+|  inf | 1 | replace | replace | replace | replace | replace | replace |
+"
+  "Special numbers for Calc formula.")
+
+(ert-deftest test-org-table/references/format-specifier-EL ()
+  "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row).  Format specifier EL."
+  ;; Empty fields are kept during parsing field but lost as list
+  ;; elements within Lisp formula syntactically when used literally
+  ;; and not enclosed with " within fields, see last columns with len.
+  (org-test-table-target-expect
+   references/target-normal
+   ;; All the #ERROR show that for Lisp calculations N has to be used.
+   "
+| 0 | 1 | 0 |      1 |      1 |      1 | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+|   | 1 |   |      1 |      1 |      1 | 1 | 1 |
+|   |   |   |      0 |      0 |      0 | 0 | 0 |
+"
+   1 (concat
+      "#+TBLFM: $3 = '(identity \"$1\"); EL :: $4 = '(+ $1 $2); EL :: "
+      "$5 = '(+ $1..$2); EL :: $6 = '(+ @0$1..@0$2); EL :: "
+      "$7 = '(length '($1..$2)); EL :: $8 = '(length '(@0$1..@0$2)); EL"))
+
+  ;; Empty fields are kept during parsing field _and_ as list elements
+  ;; within Lisp formula syntactically even when used literally when
+  ;; enclosed with " within fields, see last columns with len.
+  (org-test-table-target-expect
+   "
+| \"0\" | \"1\" | repl | repl | repl | repl | repl | repl |
+| \"z\" | \"1\" | repl | repl | repl | repl | repl | repl |
+| \"\"  | \"1\" | repl | repl | repl | repl | repl | repl |
+| \"\"  | \"\"  | repl | repl | repl | repl | repl | repl |
+"
+   "
+| \"0\" | \"1\" | \"0\" | 1 | #ERROR | #ERROR | 2 | 2 |
+| \"z\" | \"1\" | \"z\" | 1 | #ERROR | #ERROR | 2 | 2 |
+| \"\"  | \"1\" | \"\"  | 1 | #ERROR | #ERROR | 2 | 2 |
+| \"\"  | \"\"  | \"\"  | 0 | #ERROR | #ERROR | 2 | 2 |
+"
+   1 (concat
+      "#+TBLFM: $3 = '(concat \"\\\"\" $1 \"\\\"\"); EL :: "
+      "$4 = '(+ (string-to-number $1) (string-to-number $2)); EL :: "
+      "$5 = '(+ $1..$2); EL :: $6 = '(+ @0$1..@0$2); EL :: "
+      "$7 = '(length '($1..$2)); EL :: $8 = '(length '(@0$1..@0$2)); EL")))
+
+(ert-deftest test-org-table/references/format-specifier-E ()
+  "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row).  Format specifier E."
+  (let ((lisp
+	 (concat
+	  "#+TBLFM: $3 = '(identity $1); E :: $4 = '(+ $1 $2); E :: "
+	  "$5 = '(+ $1..$2); E :: $6 = '(+ @0$1..@0$2); E :: "
+	  "$7 = '(length '($1..$2)); E :: $8 = '(length '(@0$1..@0$2)); E"))
+	(calc
+	 (concat
+	  "#+TBLFM: $3 = $1; E :: $4 = $1 + $2; E :: "
+	  "$5 = vsum($1..$2); E :: $6 = vsum(@0$1..@0$2); E :: "
+	  "$7 = vlen($1..$2); E :: $8 = vlen(@0$1..@0$2); E")))
+    (org-test-table-target-expect
+     references/target-normal
+     ;; All the #ERROR show that for Lisp calculations N has to be used.
+     "
+| 0 | 1 | 0 | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+|   | 1 |   | #ERROR | #ERROR | #ERROR | 2 | 2 |
+|   |   |   | #ERROR | #ERROR | #ERROR | 2 | 2 |
+"
+     1 lisp)
+    (org-test-table-target-expect
+     references/target-normal
+     "
+| 0 | 1 | 0 |     1 | 1      | 1      |      2 |      2 |
+| z | 1 | z | z + 1 | z + 1  | z + 1  |      2 |      2 |
+|   | 1 | 0 |     1 | #ERROR | #ERROR | #ERROR | #ERROR |
+|   |   | 0 |     0 | #ERROR | #ERROR | #ERROR | #ERROR |
+"
+     1 calc)
+    (org-test-table-target-expect
+     references/target-special
+     "
+|  nan | 1 |  nan |  nan |  nan |  nan | 2 | 2 |
+| uinf | 1 | uinf | uinf | uinf | uinf | 2 | 2 |
+| -inf | 1 | -inf | -inf | -inf | -inf | 2 | 2 |
+|  inf | 1 |  inf |  inf |  inf |  inf | 2 | 2 |
+"
+     1 calc)))
+
+(ert-deftest test-org-table/references/format-specifier-EN ()
+  "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row).  Format specifier EN."
+  (let ((lisp (concat
+	       "#+TBLFM: $3 = '(identity $1); EN :: $4 = '(+ $1 $2); EN :: "
+	       "$5 = '(+ $1..$2); EN :: $6 = '(+ @0$1..@0$2); EN :: "
+	       "$7 = '(length '($1..$2)); EN :: "
+	       "$8 = '(length '(@0$1..@0$2)); EN"))
+	(calc (concat
+	       "#+TBLFM: $3 = $1; EN :: $4 = $1 + $2; EN :: "
+	       "$5 = vsum($1..$2); EN :: $6 = vsum(@0$1..@0$2); EN :: "
+	       "$7 = vlen($1..$2); EN :: $8 = vlen(@0$1..@0$2); EN")))
+    (org-test-table-target-expect
+     references/target-normal
+     "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+|   | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+|   |   | 0 | 0 | 0 | 0 | 2 | 2 |
+"
+     1 lisp calc)
+    (org-test-table-target-expect
+     references/target-special
+     "
+|  nan | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| uinf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| -inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+|  inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+"
+     1 calc)))
+
+(ert-deftest test-org-table/references/format-specifier-L ()
+  "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row).  Format specifier L."
+  (org-test-table-target-expect
+   references/target-normal
+   ;; All the #ERROR show that for Lisp calculations N has to be used.
+   ;; TODO: Len for range reference with only empty fields should be 0.
+   "
+| 0 | 1 | 0 |      1 |      1 |      1 | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+|   | 1 |   |      1 |      1 |      1 | 1 | 1 |
+|   |   |   |      0 |      0 |      0 | 1 | 1 |
+"
+   1 (concat
+      "#+TBLFM: $3 = '(identity \"$1\"); L :: $4 = '(+ $1 $2); L :: "
+      "$5 = '(+ $1..$2); L :: $6 = '(+ @0$1..@0$2); L :: "
+      "$7 = '(length '($1..$2)); L :: $8 = '(length '(@0$1..@0$2)); L")))
+
+(ert-deftest test-org-table/references/format-specifier-none ()
+  "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row).  No format specifier."
+  (let ((lisp (concat
+	       "#+TBLFM: $3 = '(identity $1) :: $4 = '(+ $1 $2) :: "
+	       "$5 = '(+ $1..$2) :: $6 = '(+ @0$1..@0$2) :: "
+	       "$7 = '(length '($1..$2)) :: $8 = '(length '(@0$1..@0$2))"))
+	(calc (concat
+	       "#+TBLFM: $3 = $1 :: $4 = $1 + $2 :: "
+	       "$5 = vsum($1..$2) :: $6 = vsum(@0$1..@0$2) :: "
+	       "$7 = vlen($1..$2) :: $8 = vlen(@0$1..@0$2)")))
+    (org-test-table-target-expect
+     references/target-normal
+     ;; All the #ERROR show that for Lisp calculations N has to be used.
+     "
+| 0 | 1 | 0 | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+|   | 1 |   | #ERROR | #ERROR | #ERROR | 1 | 1 |
+|   |   |   | #ERROR | #ERROR | #ERROR | 1 | 1 |
+"
+     1 lisp)
+    (org-test-table-target-expect
+     references/target-normal
+     "
+| 0 | 1 | 0 |     1 |     1 |     1 | 2 | 2 |
+| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 |
+|   | 1 | 0 |     1 |     1 |     1 | 1 | 1 |
+|   |   | 0 |     0 |     0 |     0 | 1 | 1 |
+"
+     1 calc)
+    (org-test-table-target-expect
+     references/target-special
+     "
+|  nan | 1 |  nan |  nan |  nan |  nan | 2 | 2 |
+| uinf | 1 | uinf | uinf | uinf | uinf | 2 | 2 |
+| -inf | 1 | -inf | -inf | -inf | -inf | 2 | 2 |
+|  inf | 1 |  inf |  inf |  inf |  inf | 2 | 2 |
+"
+     1 calc)))
+
+(ert-deftest test-org-table/references/format-specifier-N ()
+  "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row).  Format specifier N."
+  (let ((lisp
+	 (concat
+	  "#+TBLFM: $3 = '(identity $1); N :: $4 = '(+ $1 $2); N :: "
+	  "$5 = '(+ $1..$2); N :: $6 = '(+ @0$1..@0$2); N :: "
+	  "$7 = '(length '($1..$2)); N :: $8 = '(length '(@0$1..@0$2)); N"))
+        (calc
+	 (concat
+	  "#+TBLFM: $3 = $1; N :: $4 = $1 + $2; N :: "
+	  "$5 = vsum($1..$2); N :: $6 = vsum(@0$1..@0$2); N :: "
+	  "$7 = vlen($1..$2); N :: $8 = vlen(@0$1..@0$2); N")))
+    (org-test-table-target-expect
+     references/target-normal
+     ;; TODO: Len for simple range reference with empty field should
+     ;; also be 1
+     "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+|   | 1 | 0 | 1 | 1 | 1 | 2 | 1 |
+|   |   | 0 | 0 | 0 | 0 | 2 | 1 |
+"
+     1 lisp calc)
+    (org-test-table-target-expect
+     references/target-special
+     "
+|  nan | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| uinf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| -inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+|  inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+"
+     1 calc)))
+
+(ert-deftest test-org-table/compare ()
+  "Basic: Compare field references in Calc."
+  (org-test-table-target-expect
+   "
+|      | 0    | z    | nan  | uinf | -inf | inf  |
+|------+------+------+------+------+------+------|
+|    0 | repl | repl | repl | repl | repl | repl |
+|    z | repl | repl | repl | repl | repl | repl |
+|  nan | repl | repl | repl | repl | repl | repl |
+| uinf | repl | repl | repl | repl | repl | repl |
+| -inf | repl | repl | repl | repl | repl | repl |
+|  inf | repl | repl | repl | repl | repl | repl |
+"
+   "
+|      | 0 | z | nan | uinf | -inf | inf |
+|------+---+---+-----+------+------+-----|
+|    0 | x |   |     |      |      |     |
+|    z |   | x |     |      |      |     |
+|  nan |   |   |   x |      |      |     |
+| uinf |   |   |     |    x |      |     |
+| -inf |   |   |     |      |    x |     |
+|  inf |   |   |     |      |      |   x |
+"
+   1
+   ;; Compare field reference ($1) with field reference (@1)
+   "#+TBLFM: @I$<<..@>$> = if(\"$1\" = \"@1\", x, string(\"\")); E"
+   ;; Compare field reference ($1) with absolute term
+   (concat "#+TBLFM: "
+	   "$2 = if(\"$1\" = \"(0)\"   , x, string(\"\")); E :: "
+	   "$3 = if(\"$1\" = \"(z)\"   , x, string(\"\")); E :: "
+	   "$4 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: "
+	   "$5 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: "
+	   "$6 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: "
+	   "$7 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E"))
+
+  ;; Check field reference converted from an empty field: Despite this
+  ;; field reference will not end up in a result, Calc evaluates it.
+  ;; Make sure that also then there is no Calc error.
+  (org-test-table-target-expect
+   "
+|   0 | replace |
+|   z | replace |
+| nan | replace |
+"
+   "
+|   0 |     1 |
+|   z | z + 1 |
+| nan |   nan |
+"
+   1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"))
+
+(ert-deftest test-org-table/empty-field ()
+  "Examples how to deal with empty fields."
+  ;; Empty fields in simple and complex range reference: Suppress them
+  ;; ($5 and $6) or keep them and use 0 ($7 and $8)
+
+  ;; Calc formula
+  (org-test-table-target-expect
+   "\n|   |   | 5 | 7 | replace | replace | replace | replace |\n"
+   "\n|   |   | 5 | 7 | 6 | 6 | 3 | 3 |\n"
+   1
+   (concat "#+TBLFM: "
+	   "$5 = vmean($1..$4)     :: $6 = vmean(@0$1..@0$4) :: "
+	   "$7 = vmean($1..$4); EN :: $8 = vmean(@0$1..@0$4); EN"))
+
+  ;; Lisp formula
+  ;; TODO: Len for simple range reference with empty field should also
+  ;; be 6
+  (org-test-table-target-expect
+   "\n|   |   | 5 | 7 | replace | replace | replace | replace |\n"
+   "\n|   |   | 5 | 7 | 3 | 6 | 3 | 3 |\n"
+   1
+   (concat "#+TBLFM: "
+	   "$5 = '(/ (+   $1..$4  ) (length '(  $1..$4  )));  N :: "
+	   "$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4)));  N :: "
+	   "$7 = '(/ (+   $1..$4  ) (length '(  $1..$4  ))); EN :: "
+	   "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN")))
+
+;; End of table examples and beginning of internal tests.
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-EL ()
+  (fset 'f 'org-table-make-reference)
+  ;; For Lisp formula only
+  (should (equal "0"   (f   "0"      t nil 'literal)))
+  (should (equal "z"   (f   "z"      t nil 'literal)))
+  (should (equal  ""   (f   ""       t nil 'literal)))
+  (should (equal "0 1" (f '("0" "1") t nil 'literal)))
+  (should (equal "z 1" (f '("z" "1") t nil 'literal)))
+  (should (equal  " 1" (f '(""  "1") t nil 'literal)))
+  (should (equal  " "  (f '(""  "" ) t nil 'literal))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-E ()
+  (fset 'f 'org-table-make-reference)
+  ;; For Lisp formula
+  (should (equal "\"0\""       (f   "0"         t nil t)))
+  (should (equal "\"z\""       (f   "z"         t nil t)))
+  (should (equal  "\"\""       (f   ""          t nil t)))
+  (should (equal "\"0\" \"1\"" (f '("0"    "1") t nil t)))
+  (should (equal "\"z\" \"1\"" (f '("z"    "1") t nil t)))
+  (should (equal  "\"\" \"1\"" (f '(""     "1") t nil t)))
+  (should (equal  "\"\" \"\""  (f '(""     "" ) t nil t)))
+  ;; For Calc formula
+  (should (equal  "(0)"        (f   "0"         t nil nil)))
+  (should (equal  "(z)"        (f   "z"         t nil nil)))
+  (should (equal  "(0)"        (f   ""          t nil nil)))
+  (should (equal  "[0,1]"      (f '("0"    "1") t nil nil)))
+  (should (equal  "[z,1]"      (f '("z"    "1") t nil nil)))
+  (should (equal   "[,1]"      (f '(""     "1") t nil nil)))
+  (should (equal   "[,]"       (f '(""     "" ) t nil nil)))
+  ;; For Calc formula, special numbers
+  (should (equal  "(nan)"      (f    "nan"      t nil nil)))
+  (should (equal "(uinf)"      (f   "uinf"      t nil nil)))
+  (should (equal "(-inf)"      (f   "-inf"      t nil nil)))
+  (should (equal  "(inf)"      (f    "inf"      t nil nil)))
+  (should (equal  "[nan,1]"    (f '( "nan" "1") t nil nil)))
+  (should (equal "[uinf,1]"    (f '("uinf" "1") t nil nil)))
+  (should (equal "[-inf,1]"    (f '("-inf" "1") t nil nil)))
+  (should (equal  "[inf,1]"    (f '( "inf" "1") t nil nil))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-EN ()
+  (fset 'f 'org-table-make-reference)
+  ;; For Lisp formula
+  (should (equal  "0"    (f   "0"         t t t)))
+  (should (equal  "0"    (f   "z"         t t t)))
+  (should (equal  "0"    (f   ""          t t t)))
+  (should (equal  "0 1"  (f '("0"    "1") t t t)))
+  (should (equal  "0 1"  (f '("z"    "1") t t t)))
+  (should (equal  "0 1"  (f '(""     "1") t t t)))
+  (should (equal  "0 0"  (f '(""     "" ) t t t)))
+  ;; For Calc formula
+  (should (equal "(0)"   (f   "0"         t t nil)))
+  (should (equal "(0)"   (f   "z"         t t nil)))
+  (should (equal "(0)"   (f   ""          t t nil)))
+  (should (equal "[0,1]" (f '("0"    "1") t t nil)))
+  (should (equal "[0,1]" (f '("z"    "1") t t nil)))
+  (should (equal "[0,1]" (f '(""     "1") t t nil)))
+  (should (equal "[0,0]" (f '(""     "" ) t t nil)))
+  ;; For Calc formula, special numbers
+  (should (equal "(0)"   (f    "nan"      t t nil)))
+  (should (equal "(0)"   (f   "uinf"      t t nil)))
+  (should (equal "(0)"   (f   "-inf"      t t nil)))
+  (should (equal "(0)"   (f    "inf"      t t nil)))
+  (should (equal "[0,1]" (f '( "nan" "1") t t nil)))
+  (should (equal "[0,1]" (f '("uinf" "1") t t nil)))
+  (should (equal "[0,1]" (f '("-inf" "1") t t nil)))
+  (should (equal "[0,1]" (f '( "inf" "1") t t nil))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-L ()
+  (fset 'f 'org-table-make-reference)
+  ;; For Lisp formula only
+  (should (equal "0"   (f   "0"      nil nil 'literal)))
+  (should (equal "z"   (f   "z"      nil nil 'literal)))
+  (should (equal  ""   (f   ""       nil nil 'literal)))
+  (should (equal "0 1" (f '("0" "1") nil nil 'literal)))
+  (should (equal "z 1" (f '("z" "1") nil nil 'literal)))
+  (should (equal   "1" (f '(""  "1") nil nil 'literal)))
+  ;; TODO: Should result in empty string like with field reference of
+  ;; empty field.
+  (should (equal  "0"  (f '(""  "" ) nil nil 'literal))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-none ()
+  (fset 'f 'org-table-make-reference)
+  ;; For Lisp formula
+  (should (equal "\"0\""       (f   "0"         nil nil t)))
+  (should (equal "\"z\""       (f   "z"         nil nil t)))
+  (should (equal  "\"\""       (f   ""          nil nil t)))
+  (should (equal "\"0\" \"1\"" (f '("0"    "1") nil nil t)))
+  (should (equal "\"z\" \"1\"" (f '("z"    "1") nil nil t)))
+  (should (equal       "\"1\"" (f '(""     "1") nil nil t)))
+  ;; TODO: Should result in empty string like with field reference of
+  ;; empty field.
+  (should (equal    "\"0\""    (f '(""     "" ) nil nil t)))
+  ;; For Calc formula
+  (should (equal  "(0)"        (f   "0"         nil nil nil)))
+  (should (equal  "(z)"        (f   "z"         nil nil nil)))
+  (should (equal  "(0)"        (f   ""          nil nil nil)))
+  (should (equal  "[0,1]"      (f '("0"    "1") nil nil nil)))
+  (should (equal  "[z,1]"      (f '("z"    "1") nil nil nil)))
+  (should (equal    "[1]"      (f '(""     "1") nil nil nil)))
+  (should (equal   "[0]"       (f '(""     "" ) nil nil nil)))
+  ;; For Calc formula, special numbers
+  (should (equal  "(nan)"      (f    "nan"      nil nil nil)))
+  (should (equal "(uinf)"      (f   "uinf"      nil nil nil)))
+  (should (equal "(-inf)"      (f   "-inf"      nil nil nil)))
+  (should (equal  "(inf)"      (f    "inf"      nil nil nil)))
+  (should (equal  "[nan,1]"    (f '( "nan" "1") nil nil nil)))
+  (should (equal "[uinf,1]"    (f '("uinf" "1") nil nil nil)))
+  (should (equal "[-inf,1]"    (f '("-inf" "1") nil nil nil)))
+  (should (equal  "[inf,1]"    (f '( "inf" "1") nil nil nil))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-N ()
+  (fset 'f 'org-table-make-reference)
+  ;; For Lisp formula
+  (should (equal  "0"    (f   "0"         nil t t)))
+  (should (equal  "0"    (f   "z"         nil t t)))
+  (should (equal  "0"    (f   ""          nil t t)))
+  (should (equal  "0 1"  (f '("0"    "1") nil t t)))
+  (should (equal  "0 1"  (f '("z"    "1") nil t t)))
+  (should (equal    "1"  (f '(""     "1") nil t t)))
+  (should (equal   "0"   (f '(""     "" ) nil t t)))
+  ;; For Calc formula
+  (should (equal "(0)"   (f   "0"         nil t nil)))
+  (should (equal "(0)"   (f   "z"         nil t nil)))
+  (should (equal "(0)"   (f   ""          nil t nil)))
+  (should (equal "[0,1]" (f '("0"    "1") nil t nil)))
+  (should (equal "[0,1]" (f '("z"    "1") nil t nil)))
+  (should (equal   "[1]" (f '(""     "1") nil t nil)))
+  (should (equal  "[0]"  (f '(""     "" ) nil t nil)))
+  ;; For Calc formula, special numbers
+  (should (equal "(0)"   (f    "nan"      nil t nil)))
+  (should (equal "(0)"   (f   "uinf"      nil t nil)))
+  (should (equal "(0)"   (f   "-inf"      nil t nil)))
+  (should (equal "(0)"   (f    "inf"      nil t nil)))
+  (should (equal "[0,1]" (f '( "nan" "1") nil t nil)))
+  (should (equal "[0,1]" (f '("uinf" "1") nil t nil)))
+  (should (equal "[0,1]" (f '("-inf" "1") nil t nil)))
+  (should (equal "[0,1]" (f '( "inf" "1") nil t nil))))
+
 (ert-deftest test-org-table/org-table-convert-refs-to-an/1 ()
   "Simple reference @1$1."
   (should
@@ -69,29 +647,6 @@
 ;;   (should
 ;;    (string= "$3 = remote(FOO, @@#$2)" (org-table-convert-refs-to-rc "C& = remote(FOO, @@#B&)"))))
 
-(ert-deftest test-org-table/simple-formula ()
-  (org-test-with-temp-text-in-file "
-
-* simple formula
-  :PROPERTIES:
-  :ID:       563523f7-3f3e-49c9-9622-9216cc9a5d95
-  :END:
-
-#+tblname: simple-formula
-|  1 |
-|  2 |
-|  3 |
-|  4 |
-|----|
-|    |
-  #+TBLFM: $1=vsum(@1..@-1)
-"
-    (re-search-forward (regexp-quote "#+tblname: simple-formula") nil t)
-    (forward-line 1)
-    (should (org-at-table-p))
-    (should (org-table-recalculate 'all))
-    (should (string= "10" (first (nth 5 (org-table-to-lisp)))))))
-
 (provide 'test-org-table)
 
 ;;; test-org-table.el ends here
-- 
1.7.1


[-- Attachment #6: 0005-org-table.el-Fix-range-len-bugs-and-inconsistencies.patch.txt --]
[-- Type: text/plain, Size: 5830 bytes --]

From ef9b7f9dc4d9f0513b36e8630443f099280636c2 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:02:01 +0100
Subject: [PATCH 5/6] org-table.el: Fix range len bugs and inconsistencies

* lisp/org-table.el (org-table-eval-formula): Keep empty fields during
preprocessing.
(org-table-make-reference): A range with only empty fields should not
always return 0 but also empty string, consistent with field reference
of an empty field.  Use future design for nan but replicate current
behavior.
* testing/lisp/test-org-table.el: Adapt expected for several
ert-deftest.

The range len bugs may lead to wrong calculations for range references
with empty fields when the range len is relevant.  Affects typically
Calc vmean on simple range and without format specifier EN.  Also
Lisp with e. g. `length' on simple range or with L.
---
 lisp/org-table.el              |   13 ++++++++++---
 testing/lisp/test-org-table.el |   30 ++++++++----------------------
 2 files changed, 18 insertions(+), 25 deletions(-)

diff --git a/lisp/org-table.el b/lisp/org-table.el
index 6a9d0b4..1f540b4 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2557,7 +2557,10 @@ not overwrite the stored one."
 			  fields)))
 	(if (eq numbers t)
 	    (setq fields (mapcar
-			  (lambda (x) (number-to-string (string-to-number x)))
+			  (lambda (x)
+			    (if (string-match "\\S-" x)
+				(number-to-string (string-to-number x))
+			      x))
 			  fields)))
 	(setq ndown (1- ndown))
 	(setq form (copy-sequence formula)
@@ -2862,7 +2865,7 @@ LISPP means to return something appropriate for a Lisp list."
 	    (delq nil
 		  (mapcar (lambda (x) (if (string-match "\\S-" x) x nil))
 			  elements))))
-    (setq elements (or elements '("0")))
+    (setq elements (or elements '("")))
     (if lispp
 	(mapconcat
 	 (lambda (x)
@@ -2872,7 +2875,11 @@ LISPP means to return something appropriate for a Lisp list."
 	 elements " ")
       (concat "[" (mapconcat
 		   (lambda (x)
-		     (if numbers (number-to-string (string-to-number x)) x))
+		     (if (string-match "\\S-" x)
+			 (if numbers
+			     (number-to-string (string-to-number x))
+			   x)
+		       (if (or (not keep-empty) numbers) "0" "")))
 		   elements
 		   ",") "]"))))
 
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 68949d9..6133005 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -309,12 +309,11 @@ reference (with row).  Format specifier L."
   (org-test-table-target-expect
    references/target-normal
    ;; All the #ERROR show that for Lisp calculations N has to be used.
-   ;; TODO: Len for range reference with only empty fields should be 0.
    "
 | 0 | 1 | 0 |      1 |      1 |      1 | 2 | 2 |
 | z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
 |   | 1 |   |      1 |      1 |      1 | 1 | 1 |
-|   |   |   |      0 |      0 |      0 | 1 | 1 |
+|   |   |   |      0 |      0 |      0 | 0 | 0 |
 "
    1 (concat
       "#+TBLFM: $3 = '(identity \"$1\"); L :: $4 = '(+ $1 $2); L :: "
@@ -378,13 +377,11 @@ reference (with row).  Format specifier N."
 	  "$7 = vlen($1..$2); N :: $8 = vlen(@0$1..@0$2); N")))
     (org-test-table-target-expect
      references/target-normal
-     ;; TODO: Len for simple range reference with empty field should
-     ;; also be 1
      "
 | 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
 | z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
-|   | 1 | 0 | 1 | 1 | 1 | 2 | 1 |
-|   |   | 0 | 0 | 0 | 0 | 2 | 1 |
+|   | 1 | 0 | 1 | 1 | 1 | 1 | 1 |
+|   |   | 0 | 0 | 0 | 0 | 1 | 1 |
 "
      1 lisp calc)
     (org-test-table-target-expect
@@ -453,22 +450,15 @@ reference (with row).  Format specifier N."
   ;; Empty fields in simple and complex range reference: Suppress them
   ;; ($5 and $6) or keep them and use 0 ($7 and $8)
 
-  ;; Calc formula
   (org-test-table-target-expect
    "\n|   |   | 5 | 7 | replace | replace | replace | replace |\n"
    "\n|   |   | 5 | 7 | 6 | 6 | 3 | 3 |\n"
    1
+   ;; Calc formula
    (concat "#+TBLFM: "
 	   "$5 = vmean($1..$4)     :: $6 = vmean(@0$1..@0$4) :: "
-	   "$7 = vmean($1..$4); EN :: $8 = vmean(@0$1..@0$4); EN"))
-
-  ;; Lisp formula
-  ;; TODO: Len for simple range reference with empty field should also
-  ;; be 6
-  (org-test-table-target-expect
-   "\n|   |   | 5 | 7 | replace | replace | replace | replace |\n"
-   "\n|   |   | 5 | 7 | 3 | 6 | 3 | 3 |\n"
-   1
+	   "$7 = vmean($1..$4); EN :: $8 = vmean(@0$1..@0$4); EN")
+   ;; Lisp formula
    (concat "#+TBLFM: "
 	   "$5 = '(/ (+   $1..$4  ) (length '(  $1..$4  )));  N :: "
 	   "$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4)));  N :: "
@@ -553,9 +543,7 @@ reference (with row).  Format specifier N."
   (should (equal "0 1" (f '("0" "1") nil nil 'literal)))
   (should (equal "z 1" (f '("z" "1") nil nil 'literal)))
   (should (equal   "1" (f '(""  "1") nil nil 'literal)))
-  ;; TODO: Should result in empty string like with field reference of
-  ;; empty field.
-  (should (equal  "0"  (f '(""  "" ) nil nil 'literal))))
+  (should (equal  ""   (f '(""  "" ) nil nil 'literal))))
 
 (ert-deftest test-org-table/org-table-make-reference/format-specifier-none ()
   (fset 'f 'org-table-make-reference)
@@ -566,9 +554,7 @@ reference (with row).  Format specifier N."
   (should (equal "\"0\" \"1\"" (f '("0"    "1") nil nil t)))
   (should (equal "\"z\" \"1\"" (f '("z"    "1") nil nil t)))
   (should (equal       "\"1\"" (f '(""     "1") nil nil t)))
-  ;; TODO: Should result in empty string like with field reference of
-  ;; empty field.
-  (should (equal    "\"0\""    (f '(""     "" ) nil nil t)))
+  (should (equal    "\"\""     (f '(""     "" ) nil nil t)))
   ;; For Calc formula
   (should (equal  "(0)"        (f   "0"         nil nil nil)))
   (should (equal  "(z)"        (f   "z"         nil nil nil)))
-- 
1.7.1


[-- Attachment #7: 0006-Use-nan-for-empty-fields-in-Calc-formulas.patch.txt --]
[-- Type: text/plain, Size: 13560 bytes --]

From a0fcb1048ee3c638dbc5f3be98bce6b3bf0e5588 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:03:10 +0100
Subject: [PATCH 6/6] Use nan for empty fields in Calc formulas

* doc/org.texi (Formula syntax for Calc): Add explanation and example
for empty field.
* lisp/org-table.el (org-table-eval-formula): Use `keep-empty' in more
places.
(org-table-make-reference): Use nan (not a number) for empty fields in
Calc formulas.
* testing/lisp/test-org-table.el: Adapt expected for several
ert-deftest.
(test-org-table/empty-field): New examples dealing with empty fields.
(test-org-table/copy-field): New ert-deftest with examples dealing with
empty fields.

This makes it possible to write spreadsheet Calc formulas that check
for empty fields:  To sum the first two columns unless one or both
empty:
$3 = if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
---
 doc/org.texi                   |    9 ++-
 lisp/org-table.el              |   30 +++++--
 testing/lisp/test-org-table.el |  173 +++++++++++++++++++++++++++++++++-------
 3 files changed, 174 insertions(+), 38 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index 0f6297d..e282050 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2616,7 +2616,9 @@ n3 s3 e2 f4   @r{Normal, scientific, engineering, or fixed}
               @r{long as the Calc calculation precision is greater.}
 D R           @r{angle modes: degrees, radians}
 F S           @r{fraction and symbolic modes}
-E             @r{keep empty fields in ranges; else suppress empty fields in}
+E             @r{keep empty fields in ranges and use nan (not a number)}
+              @r{in Calc formulas for empty fields in range references and}
+              @r{for empty field references; else suppress empty fields in}
               @r{range references and use 0 for empty field references, see}
               @r{also the notes for `Range references' in @pxref{References}}
 N             @r{interpret all fields as numbers, use 0 for non-numbers;}
@@ -2653,7 +2655,10 @@ taylor($3,x=7,2)     @r{Taylor series of $3, at x=7, second degree}
 Calc also contains a complete set of logical operations.  For example
 
 @example
-if($1<20,teen,string(""))  @r{"teen" if age $1 less than 20, else empty}
+if($1 < 20, teen, string(""))
+                     @r{"teen" if age $1 is less than 20, else empty}
+if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
+                     @r{sum of first two columns unless one or both empty}
 @end example
 
 Note that you can also use two org-specific flags @code{T} and @code{t} for
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 1f540b4..0e7b944 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2637,7 +2637,8 @@ not overwrite the stored one."
 			   (match-string 0 form)))
 	  (setq form (replace-match
 		      (save-match-data
-			(org-table-make-reference x nil numbers lispp))
+			(org-table-make-reference
+			 x keep-empty numbers lispp))
 		      t t form)))
 
 	(if lispp
@@ -2664,7 +2665,8 @@ not overwrite the stored one."
 
 	  (setq ev (if (and duration (string-match "^[0-9]+:[0-9]+\\(?::[0-9]+\\)?$" form))
 		       form
-		     (calc-eval (cons form org-tbl-calc-modes) (if numbers 'num)))
+		     (calc-eval (cons form org-tbl-calc-modes)
+				(when (and (not keep-empty) numbers) 'num)))
 		ev (if duration (org-table-time-seconds-to-string
 				 (if (string-match "^[0-9]+:[0-9]+\\(?::[0-9]+\\)?$" ev)
 				     (string-to-number (org-table-time-string-to-seconds ev))
@@ -2851,15 +2853,27 @@ and TABLE is a vector with line types."
   "Convert list ELEMENTS to something appropriate to insert into formula.
 KEEP-EMPTY indicated to keep empty fields, default is to skip them.
 NUMBERS indicates that everything should be converted to numbers.
-LISPP means to return something appropriate for a Lisp list."
-  (if (stringp elements) ; just a single val
+LISPP non-nil means to return something appropriate for a Lisp
+list, 'literal is for the format specifier L."
+  ;; Calc nan (not a number) is used for the conversion of the empty
+  ;; field to a reference for several reasons: (i) It is accepted in a
+  ;; Calc formula (e. g. "" or "()" would result in a Calc error).
+  ;; (ii) In a single field (not in range) it can be distinguished
+  ;; from "(nan)" which is the reference made from a single field
+  ;; containing "nan".
+  (if (stringp elements)
+      ;; field reference
       (if lispp
 	  (if (eq lispp 'literal)
 	      elements
 	    (prin1-to-string (if numbers (string-to-number elements) elements)))
-	(if (equal elements "") (setq elements "0"))
-	(if numbers (setq elements (number-to-string (string-to-number elements))))
-	(concat "(" elements ")"))
+	(if (string-match "\\S-" elements)
+	    (progn
+	      (when numbers (setq elements (number-to-string
+					    (string-to-number elements))))
+	      (concat "(" elements ")"))
+	  (if (or (not keep-empty) numbers) "(0)" "nan")))
+    ;; range reference
     (unless keep-empty
       (setq elements
 	    (delq nil
@@ -2879,7 +2893,7 @@ LISPP means to return something appropriate for a Lisp list."
 			 (if numbers
 			     (number-to-string (string-to-number x))
 			   x)
-		       (if (or (not keep-empty) numbers) "0" "")))
+		       (if (or (not keep-empty) numbers) "0" "nan")))
 		   elements
 		   ",") "]"))))
 
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 6133005..4c09239 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -254,10 +254,10 @@ reference (with row).  Format specifier E."
     (org-test-table-target-expect
      references/target-normal
      "
-| 0 | 1 | 0 |     1 | 1      | 1      |      2 |      2 |
-| z | 1 | z | z + 1 | z + 1  | z + 1  |      2 |      2 |
-|   | 1 | 0 |     1 | #ERROR | #ERROR | #ERROR | #ERROR |
-|   |   | 0 |     0 | #ERROR | #ERROR | #ERROR | #ERROR |
+| 0 | 1 |   0 |     1 |     1 |     1 | 2 | 2 |
+| z | 1 |   z | z + 1 | z + 1 | z + 1 | 2 | 2 |
+|   | 1 | nan |   nan |   nan |   nan | 2 | 2 |
+|   |   | nan |   nan |   nan |   nan | 2 | 2 |
 "
      1 calc)
     (org-test-table-target-expect
@@ -398,24 +398,26 @@ reference (with row).  Format specifier N."
   "Basic: Compare field references in Calc."
   (org-test-table-target-expect
    "
-|      | 0    | z    | nan  | uinf | -inf | inf  |
-|------+------+------+------+------+------+------|
-|    0 | repl | repl | repl | repl | repl | repl |
-|    z | repl | repl | repl | repl | repl | repl |
-|  nan | repl | repl | repl | repl | repl | repl |
-| uinf | repl | repl | repl | repl | repl | repl |
-| -inf | repl | repl | repl | repl | repl | repl |
-|  inf | repl | repl | repl | repl | repl | repl |
+|      | 0    | z    |      | nan  | uinf | -inf | inf  |
+|------+------+------+------+------+------+------+------|
+|    0 | repl | repl | repl | repl | repl | repl | repl |
+|    z | repl | repl | repl | repl | repl | repl | repl |
+|      | repl | repl | repl | repl | repl | repl | repl |
+|  nan | repl | repl | repl | repl | repl | repl | repl |
+| uinf | repl | repl | repl | repl | repl | repl | repl |
+| -inf | repl | repl | repl | repl | repl | repl | repl |
+|  inf | repl | repl | repl | repl | repl | repl | repl |
 "
    "
-|      | 0 | z | nan | uinf | -inf | inf |
-|------+---+---+-----+------+------+-----|
-|    0 | x |   |     |      |      |     |
-|    z |   | x |     |      |      |     |
-|  nan |   |   |   x |      |      |     |
-| uinf |   |   |     |    x |      |     |
-| -inf |   |   |     |      |    x |     |
-|  inf |   |   |     |      |      |   x |
+|      | 0 | z |   | nan | uinf | -inf | inf |
+|------+---+---+---+-----+------+------+-----|
+|    0 | x |   |   |     |      |      |     |
+|    z |   | x |   |     |      |      |     |
+|      |   |   | x |     |      |      |     |
+|  nan |   |   |   |   x |      |      |     |
+| uinf |   |   |   |     |    x |      |     |
+| -inf |   |   |   |     |      |    x |     |
+|  inf |   |   |   |     |      |      |   x |
 "
    1
    ;; Compare field reference ($1) with field reference (@1)
@@ -424,10 +426,11 @@ reference (with row).  Format specifier N."
    (concat "#+TBLFM: "
 	   "$2 = if(\"$1\" = \"(0)\"   , x, string(\"\")); E :: "
 	   "$3 = if(\"$1\" = \"(z)\"   , x, string(\"\")); E :: "
-	   "$4 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: "
-	   "$5 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: "
-	   "$6 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: "
-	   "$7 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E"))
+	   "$4 = if(\"$1\" = \"nan\"   , x, string(\"\")); E :: "
+	   "$5 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: "
+	   "$6 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: "
+	   "$7 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: "
+	   "$8 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E"))
 
   ;; Check field reference converted from an empty field: Despite this
   ;; field reference will not end up in a result, Calc evaluates it.
@@ -436,11 +439,13 @@ reference (with row).  Format specifier N."
    "
 |   0 | replace |
 |   z | replace |
+|     | replace |
 | nan | replace |
 "
    "
 |   0 |     1 |
 |   z | z + 1 |
+|     |       |
 | nan |   nan |
 "
    1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"))
@@ -463,7 +468,119 @@ reference (with row).  Format specifier N."
 	   "$5 = '(/ (+   $1..$4  ) (length '(  $1..$4  )));  N :: "
 	   "$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4)));  N :: "
 	   "$7 = '(/ (+   $1..$4  ) (length '(  $1..$4  ))); EN :: "
-	   "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN")))
+	   "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN"))
+
+  ;; Test if one field is empty, else do a calculation
+  (org-test-table-target-expect
+   "
+| -1 | replace |
+|  0 | replace |
+|    | replace |
+"
+   "
+| -1 | 0 |
+|  0 | 1 |
+|    |   |
+"
+   1
+   ;; Calc formula
+   "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"
+   ;; Lisp formula
+   "#+TBLFM: $2 = '(if (eq \"$1\" \"\") \"\" (1+ $1)); L")
+
+  ;; Test if several fields are empty, else do a calculation
+  (org-test-table-target-expect
+   "
+| 1 | 2 | replace |
+| 4 |   | replace |
+|   | 8 | replace |
+|   |   | replace |
+"
+   "
+| 1 | 2 | 3 |
+| 4 |   |   |
+|   | 8 |   |
+|   |   |   |
+"
+   1
+   ;; Calc formula
+   (concat "#+TBLFM: $3 = if(\"$1\" = \"nan\" || \"$2\" = \"nan\", "
+	   "string(\"\"), $1 + $2); E")
+   ;; Lisp formula
+   (concat "#+TBLFM: $3 = '(if (or (eq \"$1\" \"\") (eq \"$2\" \"\")) "
+	   "\"\" (+ $1 $2)); L"))
+
+  ;; $2: Use $1 + 0.5 if $1 available, else only reformat $2 if $2 available
+  (org-test-table-target-expect
+   "
+| 1.5 | 0 |
+| 3.5 |   |
+|     | 5 |
+|     |   |
+"
+   "
+| 1.5 | 2.0 |
+| 3.5 | 4.0 |
+|     | 5.0 |
+|     |     |
+"
+   1
+   ;; Calc formula
+   (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", "
+	   "if(\"$2\" = \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1")
+   ;; Lisp formula not implemented yet
+   ))
+
+(ert-deftest test-org-table/copy-field ()
+  "Experiments on how to copy one field into another field."
+  (let ((target
+	 "
+| 0                | replace |
+| a b              | replace |
+| c   d            | replace |
+|                  | replace |
+| 2012-12          | replace |
+| [2012-12-31 Mon] | replace |
+"))
+    ;; Lisp formula to copy literally
+    (org-test-table-target-expect
+     target
+     "
+| 0                | 0                |
+| a b              | a b              |
+| c   d            | c   d            |
+|                  |                  |
+| 2012-12          | 2012-12          |
+| [2012-12-31 Mon] | [2012-12-31 Mon] |
+"
+     1 "#+TBLFM: $2 = '(identity $1)")
+
+    ;; Calc formula to copy quite literally
+    (org-test-table-target-expect
+     target
+     "
+| 0                | 0                |
+| a b              | a b              |
+| c   d            | c   d            |
+|                  |                  |
+| 2012-12          | 2012-12          |
+| [2012-12-31 Mon] | <2012-12-31 Mon> |
+"
+     1 (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", "
+	       "string(\"\"), string(subvec(\"$1\", 2, vlen(\"$1\")))); E"))
+
+    ;; Calc formula simple
+    (org-test-table-target-expect
+     target
+     "
+| 0                | 0                |
+| a b              | a b              |
+| c   d            | c d              |
+|                  |                  |
+| 2012-12          | 2000             |
+| [2012-12-31 Mon] | <2012-12-31 Mon> |
+"
+     1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1); E")))
 
 ;; End of table examples and beginning of internal tests.
 
@@ -491,11 +608,11 @@ reference (with row).  Format specifier N."
   ;; For Calc formula
   (should (equal  "(0)"        (f   "0"         t nil nil)))
   (should (equal  "(z)"        (f   "z"         t nil nil)))
-  (should (equal  "(0)"        (f   ""          t nil nil)))
+  (should (equal  "nan"        (f   ""          t nil nil)))
   (should (equal  "[0,1]"      (f '("0"    "1") t nil nil)))
   (should (equal  "[z,1]"      (f '("z"    "1") t nil nil)))
-  (should (equal   "[,1]"      (f '(""     "1") t nil nil)))
-  (should (equal   "[,]"       (f '(""     "" ) t nil nil)))
+  (should (equal  "[nan,1]"    (f '(""     "1") t nil nil)))
+  (should (equal  "[nan,nan]"  (f '(""     "" ) t nil nil)))
   ;; For Calc formula, special numbers
   (should (equal  "(nan)"      (f    "nan"      t nil nil)))
   (should (equal "(uinf)"      (f   "uinf"      t nil nil)))
-- 
1.7.1

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

[Bastien]

Hi Michael,

this is just great!  Thanks a lot for this major enhancement,
and for your details explanations and tests.

Michael Brand <michael.ch.brand@gmail.com> writes:

> Patch 6 of the attached patches makes it possible to write
> spreadsheet Calc formulas that check for empty fields: To sum the
> first two columns unless one or both empty:
> $3 = if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
>
> The other patches:
>
> - patch 1: testing/README: add howto run ERT partially

Nice.

> - patch 2: Add ERT for table alignment within Org buffer
>
>   The new function org-test-table-target-expect is to simplify writing
>   spreadsheet ERT by providing just a target table to apply the formula
>   to, the expected result table and the table formula with optionally
>   additional variants. The variants typically are to check the same
>   result with a Lisp formula and a Calc formula. test-org-table.el is
>   also a howto example collection as a user documentation.
>
> - patch 3: Extend org-table-number-regexp
>
> - patch 4: Add ERTs for basic spreadsheet functionality
>
>   My plan is to add spreadsheet examples only to Org ERT
>   (test-org-table.el) from now on and someday to move those I already
>   made on Worg.
>
> - patch 5: org-table.el: Fix range len bugs and inconsistencies
>
>   The range len bugs may lead to wrong calculations for range references
>   with empty fields when the range len is relevant.  Affects typically
>   Calc vmean on simple range and without format specifier EN.  Also
>   Lisp with e. g. `length' on simple range or with L.
>
> Note: Org ERT passes after every single patch to document the behavior
> of each patch.
>
> After the review is complete please make sure that “make check” passes
> before applying the patches and also before git push. I hope I got it
> right with the require to resolve the dependencies.

make         => fine
make single  => fine
make test    => all 339 tests passed 

I pushed the 6 commits to master.  

Thanks,

-- 
 Bastien