Tables for attendance lists - A problem understanding TBLFM?

Tables for attendance lists - A problem understanding TBLFM?

[Gunnar Wolf]

Hi,

As mostly everything for my class work is handled through org-mode, I
am trying to use it also for tracking attendance. And it almost works
(given my quite-probably-wrong way to solve it) — Can you help me
pinpoint what am I doing wrong?

In case it's not obvious, I'm a complete Lisp newbie.

My attendance tables look similar to:

#+CAPTION: Attendances for April
|---------+-------------------+---+---+---+---+----+-------|
| Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
|---------+-------------------+---+---+---+---+----+-------|
|    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
|    5678 | Madero, Francisco | X | X | X | X |    |     4 |
|    1544 | Villa, Pancho     |   |   |   |   |    |     1 |
|    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
|---------+-------------------+---+---+---+---+----+-------|
| Day avg | 2.25              | 2 | 2 | 3 | 2 |  1 |  2.50 |
|---------+-------------------+---+---+---+---+----+-------|
#+tblfm: @2$8..@5$8='(length '($3..$7))::@6$2=vmean($3..$7);%.2f::@6$3..@6$7='(length '(@2..@5))::@6$8=vmean(@2..@5);%.2f

The formula tries to fill all of the numeric values (last row and
column), but has two important bugs. First, the (attempted) logic:

- Last column includes the sum of nonempty cells. I did this by
  counting the vector's length — But, as you can see, Pancho Villa
  lives up to his rebel's name and has consistently failed to come to
  class. Still, his entry shows he attended once.

  Digging a bit, I found (via M-:) that while (vconcat "X" nil "X" "X")
  yields a three element vector [88 88 88], and (vconcat nil nil nil)
  correctly yields an empty vector []. Getting the length of []
  correctly gives 0. So, I must be doing something wrong. Can you spot
  it?

- This same problem happens of course, getting the daily averages —
  We haven't yet reached April 10, but it shows one attendance.

So, is there a way to unb0rk my lists? Org-mode seems to be quite well
equiped for this task, and I'd hate to use other tools if I can stick
to this :)

Re: Tables for attendance lists - A problem understanding TBLFM?

[Suvayu Ali]

On Mon, Apr 08, 2013 at 06:57:53PM -0500, Gunnar Wolf wrote:
> #+CAPTION: Attendances for April
> |---------+-------------------+---+---+---+---+----+-------|
> | Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
> |---------+-------------------+---+---+---+---+----+-------|
> |    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
> |    5678 | Madero, Francisco | X | X | X | X |    |     4 |
> |    1544 | Villa, Pancho     |   |   |   |   |    |     1 |
> |    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
> |---------+-------------------+---+---+---+---+----+-------|
> | Day avg | 2.25              | 2 | 2 | 3 | 2 |  1 |  2.50 |
> |---------+-------------------+---+---+---+---+----+-------|
> #+tblfm: @2$8..@5$8='(length '($3..$7))::@6$2=vmean($3..$7);%.2f::@6$3..@6$7='(length '(@2..@5))::@6$8=vmean(@2..@5);%.2f

Probably not what you were looking for, why complicate things by using
Xs instead of just a numeral like 1?  If you use 1, then you can use a
column formula; maybe something like $>=sum($3..$-1).

-- 
Suvayu

Open source is the future. It sets us free.

Re: Tables for attendance lists - A problem understanding TBLFM?

[Gunnar Wolf]

Suvayu Ali dijo [Tue, Apr 09, 2013 at 02:25:19AM +0200]:
> On Mon, Apr 08, 2013 at 06:57:53PM -0500, Gunnar Wolf wrote:
> > #+CAPTION: Attendances for April
> > |---------+-------------------+---+---+---+---+----+-------|
> > | Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
> > |---------+-------------------+---+---+---+---+----+-------|
> > |    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
> > |    5678 | Madero, Francisco | X | X | X | X |    |     4 |
> > |    1544 | Villa, Pancho     |   |   |   |   |    |     1 |
> > |    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
> > |---------+-------------------+---+---+---+---+----+-------|
> > | Day avg | 2.25              | 2 | 2 | 3 | 2 |  1 |  2.50 |
> > |---------+-------------------+---+---+---+---+----+-------|
> > #+tblfm: @2$8..@5$8='(length '($3..$7))::@6$2=vmean($3..$7);%.2f::@6$3..@6$7='(length '(@2..@5))::@6$8=vmean(@2..@5);%.2f
> 
> Probably not what you were looking for, why complicate things by using
> Xs instead of just a numeral like 1?  If you use 1, then you can use a
> column formula; maybe something like $>=sum($3..$-1).

I had though about it, and it's currently a last resort for me. I want
to publish the lists in the course's webpage, and while a '1' would do
for me, I'd rather present to the students something they are more
likely to understand as natural.

Re: Tables for attendance lists - A problem understanding TBLFM?

[Nick Dokos]

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


On Mon, Apr 8, 2013 at 10:21 PM, Gunnar Wolf <gwolf@gwolf.org> wrote:

> Suvayu Ali dijo [Tue, Apr 09, 2013 at 02:25:19AM +0200]:
> > On Mon, Apr 08, 2013 at 06:57:53PM -0500, Gunnar Wolf wrote:
> > > #+CAPTION: Attendances for April
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > | Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > |    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
> > > |    5678 | Madero, Francisco | X | X | X | X |    |     4 |
> > > |    1544 | Villa, Pancho     |   |   |   |   |    |     1 |
> > > |    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > | Day avg | 2.25              | 2 | 2 | 3 | 2 |  1 |  2.50 |
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > #+tblfm: @2$8..@5$8='(length '($3..$7))::@6$2=vmean($3..$7);%.2f::@6
> $3..@6$7='(length '(@2..@5))::@6$8=vmean(@2..@5);%.2f
> >
> > Probably not what you were looking for, why complicate things by using
> > Xs instead of just a numeral like 1?  If you use 1, then you can use a
> > column formula; maybe something like $>=sum($3..$-1).
>
> I had though about it, and it's currently a last resort for me. I want
> to publish the lists in the course's webpage, and while a '1' would do
> for me, I'd rather present to the students something they are more
> likely to understand as natural.
>
>


You can turn on formula debugging with C-c { and then you'd
see that in Pancho's case, the list is ("") i.e. a list containing the
empty string - a list of length 1. That might qualify as a bug (or not) but
 
you can easily work around it, using (delq "" list) which deletes empty
 
strings from the list.

Try this:

#+CAPTION: Attendances for April
|---------+-------------------+---+---+---+---+----+-------|
| Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
|---------+-------------------+---+---+---+---+----+-------|
|    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
|    5678 | Madero, Francisco | X | X | X | X |    |     4 |
|    1544 | Villa, Pancho     |   |   |   |   |    |     0 |
|    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
|---------+-------------------+---+---+---+---+----+-------|
#+tblfm: @II+1$>..@III-1$>='(length (delq "" '($<<<..$>>)))


I kept just one formula for clarity. I also tried to avoid absolute
row and column numbers (the $3 seemed better than $<<< though
so I kept it)
: @II is the second separator, $> is the last column
and $>> is the penultimate column.

HTH,
Nick



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

Re: Tables for attendance lists - A problem understanding TBLFM?

[Nick Dokos]

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

> ...the $3 seemed better than $<<< though so I kept it)

Bah, humbug: I pasted the $<<< version, instead of the $3 version.



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

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

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

On Tue, Apr 9, 2013 at 5:31 AM, Nick Dokos <ndokos@gmail.com> wrote:
> You can turn on formula debugging with C-c { and then you'd
> see that in Pancho's case, the list is ("") i.e. a list containing the
> empty string - a list of length 1. That might qualify as a bug (or not)

This issue is part of some old bugs that I discovered end of 2012. It
seems like my patch from then
http://orgmode.org/w/org-mode.git?p=org-mode.git;a=commitdiff;h=764315
resolved it only partially and I missed the case of a range with only
empty fields, although I tested and approved it in my ERTs... The
attached patch corrects.

It is worth a small compatibility change: For a range with only empty
fields it is now possible and necessary to choose different behaviors
of vmean by adding the format specifiers E and/or N.

Michael

[-- Attachment #2: 0001-org-table.el-Fix-range-len-bugs-for-empty-ranges.patch.txt --]
[-- Type: text/plain, Size: 7756 bytes --]

From 758414846936936f4e985fdb4de82eb7c95f163f Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Tue, 9 Apr 2013 14:35:21 +0200
Subject: [PATCH] org-table.el: Fix range len bugs for empty ranges

(org-table-make-reference): A range with only empty fields should lead
to length 0.
* 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.

It is worth a small compatibility change: For a range with only empty
fields it is now possible and necessary to choose different behaviors
of vmean by adding the format specifiers E and/or N.

This is a follow-up of commit
764315b3fce26de59189b957a8049e299209043a.
---
 lisp/org-table.el              |  7 +++--
 testing/lisp/test-org-table.el | 60 ++++++++++++++++++++++++++----------------
 2 files changed, 42 insertions(+), 25 deletions(-)

diff --git a/lisp/org-table.el b/lisp/org-table.el
index bdf4ad8..7122c87 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2929,7 +2929,10 @@ list, 'literal is for the format specifier L."
       (if lispp
 	  (if (eq lispp 'literal)
 	      elements
-	    (prin1-to-string (if numbers (string-to-number elements) elements)))
+	    (if (and (eq elements "") (not keep-empty))
+		""
+	      (prin1-to-string
+	       (if numbers (string-to-number elements) elements))))
 	(if (string-match "\\S-" elements)
 	    (progn
 	      (when numbers (setq elements (number-to-string
@@ -2942,7 +2945,7 @@ list, 'literal is for the format specifier L."
 	    (delq nil
 		  (mapcar (lambda (x) (if (string-match "\\S-" x) x nil))
 			  elements))))
-    (setq elements (or elements '("")))
+    (setq elements (or elements '()))  ; if delq returns nil then we need '()
     (if lispp
 	(mapconcat
 	 (lambda (x)
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 01adf52..2dd5f38 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -339,7 +339,7 @@ reference (with row).  No format specifier."
 | 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 |
+|   |   |   | #ERROR | 0      | 0      | 0 | 0 |
 "
      1 lisp)
     (org-test-table-target-expect
@@ -348,7 +348,7 @@ reference (with row).  No format specifier."
 | 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 |
+|   |   | 0 |     0 |     0 |     0 | 0 | 0 |
 "
      1 calc)
     (org-test-table-target-expect
@@ -381,7 +381,7 @@ reference (with row).  Format specifier N."
 | 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
 | z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
 |   | 1 | 0 | 1 | 1 | 1 | 1 | 1 |
-|   |   | 0 | 0 | 0 | 0 | 1 | 1 |
+|   |   | 0 | 0 | 0 | 0 | 0 | 0 |
 "
      1 lisp calc)
     (org-test-table-target-expect
@@ -455,20 +455,34 @@ 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)
 
-  (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
-   (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"))
+  (let ((calc (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 (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")))
+    (org-test-table-target-expect
+     "\n|   |   | 5 | 7 | replace | replace | replace | replace |\n"
+     "\n|   |   | 5 | 7 | 6 | 6 | 3 | 3 |\n"
+     1 calc lisp)
+
+    ;; The mean value of a range with only empty fields is not defined
+    (let ((target
+	   "\n|   |   |   |   | replace | replace | replace | replace |\n"))
+      (org-test-table-target-expect
+       target
+       "\n|   |   |   |   | vmean([]) | vmean([]) | 0 | 0 |\n"
+       1 calc)
+      (org-test-table-target-expect
+       target
+       "\n|   |   |   |   | #ERROR | #ERROR | 0 | 0 |\n"
+       1 lisp)))
 
   ;; Test if one field is empty, else do a calculation
   (org-test-table-target-expect
@@ -667,11 +681,11 @@ reference (with row).  Format specifier N."
   ;; 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   ""          (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)))
-  (should (equal    "\"\""     (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)))
@@ -679,7 +693,7 @@ reference (with row).  Format specifier N."
   (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)))
+  (should (equal   "[]"        (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)))
@@ -695,11 +709,11 @@ reference (with row).  Format specifier N."
   ;; 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  ""     (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)))
+  (should (equal   ""    (f '(""     "" ) nil t t)))
   ;; For Calc formula
   (should (equal "(0)"   (f   "0"         nil t nil)))
   (should (equal "(0)"   (f   "z"         nil t nil)))
@@ -707,7 +721,7 @@ reference (with row).  Format specifier N."
   (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)))
+  (should (equal  "[]"   (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)))
-- 
1.8.1.2

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

Hi Gunnar

On Tue, Apr 9, 2013 at 1:57 AM, Gunnar Wolf <gwolf@gwolf.org> wrote:

> #+tblfm: @2$8..@5$8='(length '($3..$7))::@6$2=vmean($3..$7);%.2f::@6$3..@6$7='(length '(@2..@5))::@6$8=vmean(@2..@5);%.2f

I would use

#+TBLFM: $8 = vlen($3..$7) :: @>$2 = vmean($3..$7); E %.2f ::
@>$3..@>$7 = vlen(@II..@III) :: @>$8 = vmean(@2..@5); E %.2f

or

#+TBLFM: $8 = vlen($3..$7) :: @>$2 = vmean($3..$7) +.0; E f-2 ::
@>$3..@>$7 = vlen(@II..@III) :: @>$8 = vmean(@2..@5) +.0; E f-2

For +.0 and f-2 see
http://orgmode.org/worg/org-faq.html#table-float-fraction

Michael

Re: Tables for attendance lists - A problem understanding TBLFM?

[Gunnar Wolf]

Nick Dokos dijo [Mon, Apr 08, 2013 at 11:31:14PM -0400]:
> You can turn on formula debugging with C-c { and then you'd
> see that in Pancho's case, the list is ("") i.e. a list containing the
> empty string - a list of length 1. That might qualify as a bug (or not) but
>  
> you can easily work around it, using (delq "" list) which deletes empty
> strings from the list.

Ok - I was not sure on how to use/interpret the debugging facility,
but it will surely help me

> Try this:
> 
> #+CAPTION: Attendances for April
> |---------+-------------------+---+---+---+---+----+-------|
> | Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
> |---------+-------------------+---+---+---+---+----+-------|
> |    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
> |    5678 | Madero, Francisco | X | X | X | X |    |     4 |
> |    1544 | Villa, Pancho     |   |   |   |   |    |     0 |
> |    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
> |---------+-------------------+---+---+---+---+----+-------|
> #+tblfm: @II+1$>..@III-1$>='(length (delq "" '($<<<..$>>)))

Interesting, my org-mode version behaves differently, and still gives
'1' for the empty row with your version:

Substitution history of formula
(...)
@r$c-> '(length (delq "" '("0")))
$1->   '(length (delq "" '("0")))
Result: 1

I tried substituting the empty quotes in the 'delq' with "0", '(""),
'("0")... but always got the same result: 1.

> I kept just one formula for clarity. I also tried to avoid absolute
> row and column numbers (the $3 seemed better than $<<< though
> so I kept it)
> : @II is the second separator, $> is the last column
> and $>> is the penultimate column.

Right, I want to better understand relative addressing, as each month
has a different number of columns (and some have different number of
rows), and I'd prefer having the same formulas everywhere. How would
you suggest me to address "from the third and until the next-to-last
column"?

Re: Tables for attendance lists - A problem understanding TBLFM?

[Gunnar Wolf]

Michael Brand dijo [Tue, Apr 09, 2013 at 02:40:06PM +0200]:
> On Tue, Apr 9, 2013 at 5:31 AM, Nick Dokos <ndokos@gmail.com> wrote:
> > You can turn on formula debugging with C-c { and then you'd
> > see that in Pancho's case, the list is ("") i.e. a list containing the
> > empty string - a list of length 1. That might qualify as a bug (or not)
> 
> This issue is part of some old bugs that I discovered end of 2012. It
> seems like my patch from then
> http://orgmode.org/w/org-mode.git?p=org-mode.git;a=commitdiff;h=764315
> resolved it only partially and I missed the case of a range with only
> empty fields, although I tested and approved it in my ERTs... The
> attached patch corrects.
> 
> It is worth a small compatibility change: For a range with only empty
> fields it is now possible and necessary to choose different behaviors
> of vmean by adding the format specifiers E and/or N.

Yes - it seems it is this bug you mention. I prefer not to patch my
.el if possible, as being me a non-hard-core, non-bleeding-edge
Org-mode user, I prefer following what comes in my Debian package ;-)

Re: Tables for attendance lists - A problem understanding TBLFM?

[Nick Dokos]

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

On Tue, Apr 9, 2013 at 10:55 AM, Gunnar Wolf <gwolf@gwolf.org> wrote:
>
> > Try this:
> >
> > #+CAPTION: Attendances for April
> > |---------+-------------------+---+---+---+---+----+-------|
> > | Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
> > |---------+-------------------+---+---+---+---+----+-------|
> > |    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
> > |    5678 | Madero, Francisco | X | X | X | X |    |     4 |
> > |    1544 | Villa, Pancho     |   |   |   |   |    |     0 |
> > |    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
> > |---------+-------------------+---+---+---+---+----+-------|
> > #+tblfm: @II+1$>..@III-1$>='(length (delq "" '($<<<..$>>)))
>
> Interesting, my org-mode version behaves differently, and still gives
> '1' for the empty row with your version:
>
> Substitution history of formula
> (...)
> @r$c-> '(length (delq "" '("0")))
> $1->   '(length (delq "" '("0")))
> Result: 1
>
>
Check the formula again: you seem to have captured the 0 from the last
column, instead of stopping at the penultimate column. The range should
be $3..$>> or $<<<..$>>  - also, you should have posted the whole
substitution history so we could see the range, instead of me guessing. The
motto should be "More information is better than less", but of course that
should be tempered by common sense :-)

HTH,
Nick

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

Re: Tables for attendance lists - A problem understanding TBLFM?

[Gunnar Wolf]

(Quoting in full to preserve mail readability without resorting to too
much context)

> > > #+CAPTION: Attendances for April
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > | Account | Name              | 1 | 3 | 5 | 8 | 10 | Total |
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > |    1234 | Cárdenas, Lázaro  | X |   | X | X |    |     3 |
> > > |    5678 | Madero, Francisco | X | X | X | X |    |     4 |
> > > |    1544 | Villa, Pancho     |   |   |   |   |    |     0 |
> > > |    0113 | Zapata, Emiliano  |   | X | X |   |    |     2 |
> > > |---------+-------------------+---+---+---+---+----+-------|
> > > #+tblfm: @II+1$>..@III-1$>='(length (delq "" '($<<<..$>>)))
> >
> > Interesting, my org-mode version behaves differently, and still gives
> > '1' for the empty row with your version:
> >
> > Substitution history of formula
> > (...)
> > @r$c-> '(length (delq "" '("0")))
> > $1->   '(length (delq "" '("0")))
> > Result: 1
> >
> Check the formula again: you seem to have captured the 0 from the last
> column, instead of stopping at the penultimate column. The range should
> be $3..$>> or $<<<..$>>  - also, you should have posted the whole
> substitution history so we could see the range, instead of me guessing. The
> motto should be "More information is better than less", but of course that
> should be tempered by common sense :-)

Trying with:

#+tblfm: @II+1$>..@III-1$>='(length(delq "" '($3..$>>)))

The full substitution history is:

Substitution history of formula
Orig:   '(length(delq "" '($3..$7)))
$xyz->  '(length(delq "" '($3..$7)))
@r$c->  '(length(delq "" '("0")))
$1->    '(length(delq "" '("0")))
Result: 1
Format: NONE
Final:  1

Changing the $3 for $<<< yields the exact same result. I cannot see
any "0" - As far as I can understand, the evaluation of an empty
vector for (length ...) gives this "0" ?

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

On Tue, Apr 9, 2013 at 5:10 PM, Nick Dokos <ndokos@gmail.com> wrote:
>> @r$c-> '(length (delq "" '("0")))
>> $1->   '(length (delq "" '("0")))
>> Result: 1
>>
>
> Check the formula again: you seem to have captured the 0 from the last
> column, instead of stopping at the penultimate column. The range should
> be $3..$>> or $<<<..$>>  - also, you should have posted the whole
> substitution history so we could see the range, instead of me guessing. The
> motto should be "More information is better than less", but of course that
> should be tempered by common sense :-)

Gunnar is following the Debian package, '("0") was the result for a
range with all fields empty before my first patch end of 2012. See the
second and the last two hunks of
http://orgmode.org/w/org-mode.git?p=org-mode.git;a=commitdiff;h=764315
which change the range list from '("0") to '(""). My previously
attached patch will finally change this case to the required '(), see
also its hunks at the beginning and the end.

Trying to adapt your workaround with delq to '("0") lets me give up,
also after reading the docstring of delq. Hope you or so can help.

Michael

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

> Trying to adapt your workaround with delq to '("0") lets me give up,
> also after reading the docstring of delq. Hope you or so can help.

How could I miss the here not so obvious difference between eq and equal:
(delete "0" [...]) works of course.

Michael

Re: Tables for attendance lists - A problem understanding TBLFM?

[Nick Dokos]

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

Ah, OK - I guess Gunnar will not be able to avoid an upgrade to something
more recent.

And yes, the eq/equal subtleties strike once again:

(eq "" "")
t
(eq "0" "0")
nil
(equal "0" "0")
t





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

Re: Tables for attendance lists - A problem understanding TBLFM?

[Gunnar Wolf]

Nick Dokos dijo [Tue, Apr 09, 2013 at 12:03:33PM -0400]:
> Ah, OK - I guess Gunnar will not be able to avoid an upgrade to something
> more recent.
> 
> And yes, the eq/equal subtleties strike once again:
> 
> (eq "" "")
> t
> (eq "0" "0")
> nil
> (equal "0" "0")
> t

Yay - Thanks to you all :-) Yes, it finally works fine for me with
(delete "0" [...])

Of course, at some point I will upgrade. We shall see if the world
breaks and hell ensues. But right now, I'm happy and satisfied. And
I'll study a bit more into the relative addressing you posted, as it
surely looks better suited.

Re: Tables for attendance lists - A problem understanding TBLFM?

[Bastien]

Hi Michael,

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

> This issue is part of some old bugs that I discovered end of 2012. It
> seems like my patch from then
> http://orgmode.org/w/org-mode.git?p=org-mode.git;a=commitdiff;h=764315
> resolved it only partially and I missed the case of a range with only
> empty fields, although I tested and approved it in my ERTs... The
> attached patch corrects.

Applied, thanks.

> It is worth a small compatibility change: For a range with only empty
> fields it is now possible and necessary to choose different behaviors
> of vmean by adding the format specifiers E and/or N.

I'll add this in the release notes.  Maybe you could add a footnote in
the manual for this?

Thanks,

-- 
 Bastien

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

Hi Bastien

On Tue, Apr 9, 2013 at 7:06 PM, Bastien <bzg@gnu.org> wrote:
> Maybe you could add a footnote in the manual for this?

I plan to update some paragraphs of org.texi regarding empty fields
during the next days.

Michael

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

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

Hi Bastien

On Tue, Apr 9, 2013 at 7:06 PM, Bastien <bzg@gnu.org> wrote:
>> It is worth a small compatibility change: For a range with only empty
>> fields it is now possible and necessary to choose different behaviors
>> of vmean by adding the format specifiers E and/or N.
>
> I'll add this in the release notes.

The wording of my paragraph quoted above is unfortunate, sorry. It is
more that I made fail what is the same as a division by 0 instead of
silently using 0 as the result for 0/0. Now one is forced to think
about the mean value of an empty vector/list (it is not defined) and
has to check for empty vector/list or consider EN, all explained with
examples for Calc vmean in the manual now. I don't think such a detail
is worth mentioning in the release notes.

But in the release notes I would generally, not particularly because
of the above, write:

If empty fields are of interest it is recommended to reread the
section "3.5.2 Formula syntax for Calc" of the manual because the
description for the mode strings has been clarified and new examples
have been added towards the end.

> Maybe you could add a footnote in the manual for this?

Do the attached patches clarify what you had in mind?

And to repeat myself: I see the directory "testing" as part of the
doc, in this case `test-org-table/empty-field'. ;-)

Michael

[-- Attachment #2: 0001-org.texi-Reformat-some-description-lists.patch.txt --]
[-- Type: text/plain, Size: 4721 bytes --]

From 55abfd1374b0741a23dab6ca16d88756d46a4c6c Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Sun, 14 Apr 2013 10:02:32 +0200
Subject: [PATCH 1/2] org.texi: Reformat some description lists

* doc/org.texi (Formula syntax for Calc, Emacs Lisp forms as
formulas): Reformat spreadsheet formula mode strings and some examples
from @example block with xy @r{yz} to @table.
---
 doc/org.texi |   67 +++++++++++++++++++++++++++++++--------------------------
 1 files changed, 36 insertions(+), 31 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index c65bdc1..c9b1f5b 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2652,23 +2652,28 @@ format, however, has been changed to @code{(float 8)} to keep tables
 compact.  The default settings can be configured using the variable
 @code{org-calc-default-modes}.
 
-@example
-p20           @r{set the internal Calc calculation precision to 20 digits}
-n3 s3 e2 f4   @r{Normal, scientific, engineering, or fixed}
-              @r{format of the result of Calc passed back to Org.}
-              @r{Calc formatting is unlimited in precision as}
-              @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 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;}
-              @r{N has higher precedence than E (for the value of the field)}
-L             @r{literal, for Lisp formulas only}
-@end example
+@table @asis
+@item @code{p20}
+Set the internal Calc calculation precision to 20 digits.
+@item @code{n3}, @code{s3}, @code{e2}, @code{f4}
+Normal, scientific, engineering or fixed format of the result of Calc passed
+back to Org.  Calc formatting is unlimited in precision as long as the Calc
+calculation precision is greater.
+@item @code{D}, @code{R}
+Angle modes: Degree, radian.
+@item @code{F}, @code{S}
+Fraction and symbolic modes.
+@item @code{E}
+Keep empty fields in ranges and use nan (not a number) in Calc formulas for
+empty fields in range references and for empty field references.  Else
+suppress empty fields in range references and use 0 for empty field
+references, see also the notes for `Range references' in @pxref{References}.
+@item @code{N}
+Interpret all fields as numbers, use 0 for non-numbers.  @code{N} has higher
+precedence than @code{E} (for the value of the field).
+@item @code{L}
+Literal, for Lisp formulas only.
+@end table
 
 @noindent
 Unless you use large integer numbers or high-precision-calculation
@@ -2698,12 +2703,12 @@ 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 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
+@table @code
+@item if($1 < 20, teen, string(""))
+"teen" if age $1 is less than 20, else empty.
+@item if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
+Sum of first two columns unless one or both empty.
+@end table
 
 Note that you can also use two org-specific flags @code{T} and @code{t} for
 durations computations @ref{Durations and time values}.
@@ -2738,14 +2743,14 @@ fields, so you can embed them in list or vector syntax.
 Here are a few examples---note how the @samp{N} mode is used when we do
 computations in Lisp:
 
-@example
-@r{Swap the first two characters of the content of column 1}
-  '(concat (substring $1 1 2) (substring $1 0 1) (substring $1 2))
-@r{Add columns 1 and 2, equivalent to Calc's @code{$1+$2}}
-  '(+ $1 $2);N
-@r{Compute the sum of columns 1--4, like Calc's @code{vsum($1..$4)}}
-  '(apply '+ '($1..$4));N
-@end example
+@table @code
+@item '(concat (substring $1 1 2) (substring $1 0 1) (substring $1 2))
+Swap the first two characters of the content of column 1.
+@item '(+ $1 $2);N
+Add columns 1 and 2, equivalent to Calc's @code{$1+$2}.
+@item '(apply '+ '($1..$4));N
+Compute the sum of columns 1 to 4, like Calc's @code{vsum($1..$4)}.
+@end table
 
 @node Durations and time values, Field and range formulas, Formula syntax for Lisp, The spreadsheet
 @subsection Durations and time values
-- 
1.7.4.2


[-- Attachment #3: 0002-Improve-doc-of-empty-fields.patch.txt --]
[-- Type: text/plain, Size: 20398 bytes --]

From 4861f2c3124475e9a48b3ae32b704327b5abea39 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Sun, 14 Apr 2013 10:03:17 +0200
Subject: [PATCH 2/2] Improve doc of empty fields

* doc/org.texi (Formula syntax for Calc): Improve the documentation of
empty fields in formulas for spreadsheet.
* testing/lisp/test-org-table.el: Rename wrongly named functions.
* testing/lisp/test-org-table.el: Use the more obvious "==" instead of
"=" for Calc conditions.
* testing/lisp/test-org-table.el (test-org-table/empty-field): Improve
the examples for vmean with empty fields.
---
 doc/org.texi                   |   84 +++++++++++++++---------
 testing/lisp/test-org-table.el |  139 +++++++++++++++++++++-------------------
 2 files changed, 126 insertions(+), 97 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index c9b1f5b..f5e5c8b 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2538,10 +2538,10 @@ $<<<..$>>     @r{start in third column, continue to the one but last}
 @end example
 
 @noindent Range references return a vector of values that can be fed
-into Calc vector functions.  Empty fields in ranges are normally
-suppressed, so that the vector contains only the non-empty fields (but
-see the @samp{E} mode switch below).  If there are no non-empty fields,
-@samp{[0]} is returned to avoid syntax errors in formulas.
+into Calc vector functions.  Empty fields in ranges are normally suppressed,
+so that the vector contains only the non-empty fields.  For other options
+with the mode switches @samp{E}, @samp{N} and examples @pxref{Formula syntax
+for Calc}.
 
 @subsubheading Field coordinates in formulas
 @cindex field coordinates
@@ -2652,6 +2652,8 @@ format, however, has been changed to @code{(float 8)} to keep tables
 compact.  The default settings can be configured using the variable
 @code{org-calc-default-modes}.
 
+@noindent List of modes:
+
 @table @asis
 @item @code{p20}
 Set the internal Calc calculation precision to 20 digits.
@@ -2660,32 +2662,39 @@ Normal, scientific, engineering or fixed format of the result of Calc passed
 back to Org.  Calc formatting is unlimited in precision as long as the Calc
 calculation precision is greater.
 @item @code{D}, @code{R}
-Angle modes: Degree, radian.
+Degree and radian angle modes of Calc.
 @item @code{F}, @code{S}
-Fraction and symbolic modes.
+Fraction and symbolic modes of Calc.
+@item @code{T}, @code{t}
+Duration computations in Calc or Lisp, @pxref{Durations and time values}.
 @item @code{E}
-Keep empty fields in ranges and use nan (not a number) in Calc formulas for
-empty fields in range references and for empty field references.  Else
-suppress empty fields in range references and use 0 for empty field
-references, see also the notes for `Range references' in @pxref{References}.
+If and how to consider empty fields.  Without @samp{E} empty fields in range
+references are suppressed so that the Calc vector or Lisp list contains only
+the non-empty fields.  With @samp{E} the empty fields are kept.  For empty
+fields in ranges or empty field references the value @samp{nan} (not a
+number) is used in Calc formulas and the empty string is used for Lisp
+formulas.  Add @samp{N} to use 0 instead for both formula types.  For the
+value of a field the mode @samp{N} has higher precedence than @samp{E}.
 @item @code{N}
-Interpret all fields as numbers, use 0 for non-numbers.  @code{N} has higher
-precedence than @code{E} (for the value of the field).
+Interpret all fields as numbers, use 0 for non-numbers.  See the next section
+to see how this is essential for computations with Lisp formulas.  In Calc
+formulas it is used only occasionally because there number strings are
+already interpreted as numbers without @samp{N}.
 @item @code{L}
-Literal, for Lisp formulas only.
+Literal, for Lisp formulas only.  See the next section.
 @end table
 
 @noindent
-Unless you use large integer numbers or high-precision-calculation
-and -display for floating point numbers you may alternatively provide a
-@code{printf} format specifier to reformat the Calc result after it has been
+Unless you use large integer numbers or high-precision-calculation and
+-display for floating point numbers you may alternatively provide a
+@samp{printf} format specifier to reformat the Calc result after it has been
 passed back to Org instead of letting Calc already do the
-formatting@footnote{The @code{printf} reformatting is limited in precision
-because the value passed to it is converted into an @code{integer} or
-@code{double}.  The @code{integer} is limited in size by truncating the
-signed value to 32 bits.  The @code{double} is limited in precision to 64
-bits overall which leaves approximately 16 significant decimal digits.}.
-A few examples:
+formatting@footnote{The @samp{printf} reformatting is limited in precision
+because the value passed to it is converted into an @samp{integer} or
+@samp{double}.  The @samp{integer} is limited in size by truncating the
+signed value to 32 bits.  The @samp{double} is limited in precision to 64
+bits overall which leaves approximately 16 significant decimal digits.}.  A
+few examples:
 
 @example
 $1+$2                @r{Sum of first and second field}
@@ -2696,23 +2705,36 @@ $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, 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
 
-Calc also contains a complete set of logical operations.  For example
+Calc also contains a complete set of logical operations, (@pxref{Logical
+Operations, , Logical Operations, calc, GNU Emacs Calc Manual}).  For example
 
 @table @code
 @item if($1 < 20, teen, string(""))
-"teen" if age $1 is less than 20, else empty.
-@item if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
-Sum of first two columns unless one or both empty.
+"teen" if age $1 is less than 20, else the Org table result field is set to
+empty with the empty string.
+@item if("$1" == "nan" || "$2" == "nan", string(""), $1 + $2); E
+Sum of the first two columns.  When at least one of the input fields is empty
+the Org table result field is set to empty.
+@item if(typeof(vmean($1..$7)) == 12, string(""), vmean($1..$7); E
+Mean value of a range unless there is any empty field.  Every field in the
+range that is empty is replaced by @samp{nan} which lets @samp{vmean} result
+in @samp{nan}.  Then @samp{typeof == 12} detects the @samp{nan} from
+@samp{vmean} and the Org table result field is set to empty.  Use this when
+the sample set is expected to never have missing values.
+@item if("$1..$7" == "[]", string(""), vmean($1..$7))
+Mean value of a range with empty fields skipped.  Every field in the range
+that is empty is skipped.  When all fields in the range are empty the mean
+value is not defined and the Org table result field is set to empty.  Use
+this when the sample set can have a variable size.
+@item vmean($1..$7); EN
+To complete the example before: Mean value of a range with empty fields
+counting as samples with value 0.  Use this only when incomplete sample sets
+should be padded with 0 to the full size.
 @end table
 
-Note that you can also use two org-specific flags @code{T} and @code{t} for
-durations computations @ref{Durations and time values}.
-
 You can add your own Calc functions defined in Emacs Lisp with @code{defmath}
 and use them in formula syntax for Calc.
 
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 2dd5f38..732ddb3 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -184,10 +184,10 @@
 "
   "Special numbers for Calc formula.")
 
-(ert-deftest test-org-table/references/format-specifier-EL ()
+(ert-deftest test-org-table/references/mode-string-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."
+reference (with row).  Mode string 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.
@@ -227,10 +227,10 @@ reference (with row).  Format specifier 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 ()
+(ert-deftest test-org-table/references/mode-string-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."
+reference (with row).  Mode string E."
   (let ((lisp
 	 (concat
 	  "#+TBLFM: $3 = '(identity $1); E :: $4 = '(+ $1 $2); E :: "
@@ -270,10 +270,10 @@ reference (with row).  Format specifier E."
 "
      1 calc)))
 
-(ert-deftest test-org-table/references/format-specifier-EN ()
+(ert-deftest test-org-table/references/mode-string-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."
+reference (with row).  Mode string EN."
   (let ((lisp (concat
 	       "#+TBLFM: $3 = '(identity $1); EN :: $4 = '(+ $1 $2); EN :: "
 	       "$5 = '(+ $1..$2); EN :: $6 = '(+ @0$1..@0$2); EN :: "
@@ -302,10 +302,10 @@ reference (with row).  Format specifier EN."
 "
      1 calc)))
 
-(ert-deftest test-org-table/references/format-specifier-L ()
+(ert-deftest test-org-table/references/mode-string-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."
+reference (with row).  Mode string L."
   (org-test-table-target-expect
    references/target-normal
    ;; All the #ERROR show that for Lisp calculations N has to be used.
@@ -320,10 +320,10 @@ reference (with row).  Format specifier 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 ()
+(ert-deftest test-org-table/references/mode-string-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."
+reference (with row).  No mode string."
   (let ((lisp (concat
 	       "#+TBLFM: $3 = '(identity $1) :: $4 = '(+ $1 $2) :: "
 	       "$5 = '(+ $1..$2) :: $6 = '(+ @0$1..@0$2) :: "
@@ -361,10 +361,10 @@ reference (with row).  No format specifier."
 "
      1 calc)))
 
-(ert-deftest test-org-table/references/format-specifier-N ()
+(ert-deftest test-org-table/references/mode-string-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."
+reference (with row).  Mode string N."
   (let ((lisp
 	 (concat
 	  "#+TBLFM: $3 = '(identity $1); N :: $4 = '(+ $1 $2); N :: "
@@ -421,16 +421,16 @@ reference (with row).  Format specifier N."
 "
    1
    ;; Compare field reference ($1) with field reference (@1)
-   "#+TBLFM: @I$<<..@>$> = if(\"$1\" = \"@1\", x, string(\"\")); E"
+   "#+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\" = \"(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"))
+	   "$2 = if(\"$1\" == \"(0)\"   , x, string(\"\")); E :: "
+	   "$3 = if(\"$1\" == \"(z)\"   , 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.
@@ -448,42 +448,10 @@ reference (with row).  Format specifier N."
 |     |       |
 | nan |   nan |
 "
-   1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"))
+   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)
-
-  (let ((calc (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 (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")))
-    (org-test-table-target-expect
-     "\n|   |   | 5 | 7 | replace | replace | replace | replace |\n"
-     "\n|   |   | 5 | 7 | 6 | 6 | 3 | 3 |\n"
-     1 calc lisp)
-
-    ;; The mean value of a range with only empty fields is not defined
-    (let ((target
-	   "\n|   |   |   |   | replace | replace | replace | replace |\n"))
-      (org-test-table-target-expect
-       target
-       "\n|   |   |   |   | vmean([]) | vmean([]) | 0 | 0 |\n"
-       1 calc)
-      (org-test-table-target-expect
-       target
-       "\n|   |   |   |   | #ERROR | #ERROR | 0 | 0 |\n"
-       1 lisp)))
-
   ;; Test if one field is empty, else do a calculation
   (org-test-table-target-expect
    "
@@ -498,7 +466,7 @@ reference (with row).  Format specifier N."
 "
    1
    ;; Calc formula
-   "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"
+   "#+TBLFM: $2 = if(\"$1\" == \"nan\", string(\"\"), $1 + 1); E"
    ;; Lisp formula
    "#+TBLFM: $2 = '(if (eq \"$1\" \"\") \"\" (1+ $1)); L")
 
@@ -518,7 +486,7 @@ reference (with row).  Format specifier N."
 "
    1
    ;; Calc formula
-   (concat "#+TBLFM: $3 = if(\"$1\" = \"nan\" || \"$2\" = \"nan\", "
+   (concat "#+TBLFM: $3 = if(\"$1\" == \"nan\" || \"$2\" == \"nan\", "
 	   "string(\"\"), $1 + $2); E")
    ;; Lisp formula
    (concat "#+TBLFM: $3 = '(if (or (eq \"$1\" \"\") (eq \"$2\" \"\")) "
@@ -540,10 +508,49 @@ reference (with row).  Format specifier N."
 "
    1
    ;; Calc formula
-   (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", "
-	   "if(\"$2\" = \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1")
+   (concat "#+TBLFM: $2 = if(\"$1\" == \"nan\", "
+	   "if(\"$2\" == \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1")
    ;; Lisp formula not implemented yet
-   ))
+   )
+
+  ;; Empty fields in simple and complex range reference
+  (org-test-table-target-expect
+   "
+|   |   |   |   | repl | repl | repl | repl | repl | repl |
+|   |   | 5 | 7 | repl | repl | repl | repl | repl | repl |
+| 1 | 3 | 5 | 7 | repl | repl | repl | repl | repl | repl |
+"
+   "
+|   |   |   |   |   |   |   |   | 0 | 0 |
+|   |   | 5 | 7 |   |   | 6 | 6 | 3 | 3 |
+| 1 | 3 | 5 | 7 | 4 | 4 | 4 | 4 | 4 | 4 |
+"
+   1
+   ;; Calc formula
+   (concat
+    "#+TBLFM: "
+    "$5 = if(typeof(vmean($1..$4)) == 12, "
+    "string(\"\"), vmean($1..$4)); E :: "
+    "$6 = if(typeof(vmean(@0$1..@0$4)) == 12, "
+    "string(\"\"), vmean(@0$1..@0$4)); E :: "
+    "$7 = if(\"$1..$4\" == \"[]\", string(\"\"), vmean($1..$4)) :: "
+    "$8 = if(\"@0$1..@0$4\" == \"[]\", string(\"\"), vmean(@0$1..@0$4)) :: "
+    "$9 = vmean($1..$4); EN :: "
+    "$10 = vmean(@0$1..@0$4); EN")
+   ;; Lisp formula
+   (concat
+    "#+TBLFM: "
+    "$5 = '(let ((l '($1..$4))) (if (member \"\" l) \"\" "
+    "(/ (apply '+ (mapcar 'string-to-number l)) (length l)))); E :: "
+    "$6 = '(let ((l '(@0$1..@0$4))) (if (member \"\" l) \"\" "
+    "(/ (apply '+ (mapcar 'string-to-number l)) (length l)))); E :: "
+    "$7 = '(let ((l '($1..$4))) "
+    "(if l (/ (apply '+ l) (length l)) \"\")); N :: "
+    "$8 = '(let ((l '(@0$1..@0$4))) "
+    "(if l (/ (apply '+ l) (length l)) \"\")); N :: "
+    "$9 = '(/ (+ $1..$4) (length '($1..$4))); EN :: "
+    "$10 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN")
+))
 
 (ert-deftest test-org-table/copy-field ()
   "Experiments on how to copy one field into another field."
@@ -580,7 +587,7 @@ reference (with row).  Format specifier N."
 | 2012-12          | 2012-12          |
 | [2012-12-31 Mon] | <2012-12-31 Mon> |
 "
-     1 (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", "
+     1 (concat "#+TBLFM: $2 = if(\"$1\" == \"nan\", "
 	       "string(\"\"), string(subvec(\"$1\", 2, vlen(\"$1\")))); E"))
 
     ;; Calc formula simple
@@ -594,11 +601,11 @@ reference (with row).  Format specifier N."
 | 2012-12          | 2000             |
 | [2012-12-31 Mon] | <2012-12-31 Mon> |
 "
-     1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1); E")))
+     1 "#+TBLFM: $2 = if(\"$1\" == \"nan\", string(\"\"), $1); E")))
 
 ;; End of table examples and beginning of internal tests.
 
-(ert-deftest test-org-table/org-table-make-reference/format-specifier-EL ()
+(ert-deftest test-org-table/org-table-make-reference/mode-string-EL ()
   (fset 'f 'org-table-make-reference)
   ;; For Lisp formula only
   (should (equal "0"   (f   "0"      t nil 'literal)))
@@ -609,7 +616,7 @@ reference (with row).  Format specifier N."
   (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 ()
+(ert-deftest test-org-table/org-table-make-reference/mode-string-E ()
   (fset 'f 'org-table-make-reference)
   ;; For Lisp formula
   (should (equal "\"0\""       (f   "0"         t nil t)))
@@ -637,7 +644,7 @@ reference (with row).  Format specifier N."
   (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 ()
+(ert-deftest test-org-table/org-table-make-reference/mode-string-EN ()
   (fset 'f 'org-table-make-reference)
   ;; For Lisp formula
   (should (equal  "0"    (f   "0"         t t t)))
@@ -665,7 +672,7 @@ reference (with row).  Format specifier N."
   (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 ()
+(ert-deftest test-org-table/org-table-make-reference/mode-string-L ()
   (fset 'f 'org-table-make-reference)
   ;; For Lisp formula only
   (should (equal "0"   (f   "0"      nil nil 'literal)))
@@ -676,7 +683,7 @@ reference (with row).  Format specifier N."
   (should (equal   "1" (f '(""  "1") nil nil 'literal)))
   (should (equal  ""   (f '(""  "" ) nil nil 'literal))))
 
-(ert-deftest test-org-table/org-table-make-reference/format-specifier-none ()
+(ert-deftest test-org-table/org-table-make-reference/mode-string-none ()
   (fset 'f 'org-table-make-reference)
   ;; For Lisp formula
   (should (equal "\"0\""       (f   "0"         nil nil t)))
@@ -704,7 +711,7 @@ reference (with row).  Format specifier N."
   (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 ()
+(ert-deftest test-org-table/org-table-make-reference/mode-string-N ()
   (fset 'f 'org-table-make-reference)
   ;; For Lisp formula
   (should (equal  "0"    (f   "0"         nil t t)))
-- 
1.7.4.2

Re: Tables for attendance lists - A problem understanding TBLFM?

[Bastien]

Hi Michael,

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

> But in the release notes I would generally, not particularly because
> of the above, write:
>
> If empty fields are of interest it is recommended to reread the
> section "3.5.2 Formula syntax for Calc" of the manual because the
> description for the mode strings has been clarified and new examples
> have been added towards the end.

I updated Changes.org...
>
>> Maybe you could add a footnote in the manual for this?
>
> Do the attached patches clarify what you had in mind?

... and applied the patches.  Thanks!

PS: you can use .patch as the extension for the patches, 
Emacs reads them using diff-mode and our .gitignore will
DTRT here.

> And to repeat myself: I see the directory "testing" as part of the
> doc, in this case `test-org-table/empty-field'. ;-)

Mhh... yes, but the tests are not in Emacs yet :)

-- 
 Bastien

Re: Tables for attendance lists - A problem understanding TBLFM?

[Michael Brand]

Hi Bastien

On Sun, Apr 14, 2013 at 12:11 PM, Bastien <bzg@gnu.org> wrote:
> PS: you can use .patch as the extension for the patches,
> Emacs reads them using diff-mode and our .gitignore will
> DTRT here.

Yes, but I do this hack of .patch.txt as a workaround to get the right
email "Content-Type: text/plain" when attaching with the web interface
of Google Gmail. There .patch or .diff results in "Content-Type:
application/octet-stream" which has to be avoided even more. I know,
but using the web interface helps me to keep it simple and usable on
any machine/device.

Michael

Re: Tables for attendance lists - A problem understanding TBLFM?

[Bastien]

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

> Yes, but I do this hack of .patch.txt as a workaround to get the right
> email "Content-Type: text/plain" when attaching with the web interface
> of Google Gmail. There .patch or .diff results in "Content-Type:
> application/octet-stream" which has to be avoided even more. I know,
> but using the web interface helps me to keep it simple and usable on
> any machine/device.

No problem!  The message is the message, not the medium.  :)

-- 
 Bastien