From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Brand Subject: Re: Distinguish between blank and zero in org-mode spreadsheet Date: Tue, 11 Dec 2012 16:14:48 +0100 Message-ID: References: <87r4mxv7l8.fsf@googlemail.com> <5596.1355206238@alphaville> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([208.118.235.92]:47230) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TiRXw-0006Ys-Ja for emacs-orgmode@gnu.org; Tue, 11 Dec 2012 10:15:10 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1TiRXl-0005bj-JV for emacs-orgmode@gnu.org; Tue, 11 Dec 2012 10:15:00 -0500 Received: from mail-vc0-f169.google.com ([209.85.220.169]:41580) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TiRXl-0005bc-FA for emacs-orgmode@gnu.org; Tue, 11 Dec 2012 10:14:49 -0500 Received: by mail-vc0-f169.google.com with SMTP id gb23so4407971vcb.0 for ; Tue, 11 Dec 2012 07:14:48 -0800 (PST) In-Reply-To: <5596.1355206238@alphaville> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Org Mode Cc: Bob Newell , Thorsten Jolitz , Nick Dokos 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 =93check if empty=94, =93range=94 and =93single fields=94. 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=92t know of any Calc formula that does this currently, so I took the one I would prefer to use. | 1 | 0 | 1 =3D && 0 =3D ? : 1 + 0 | 1 | 1 | | 1 | | 1 =3D && 0 =3D ? : 1 + 0 | | | | 1 | text | 1 =3D && text =3D ? : 1 + text | 1 + text | #ERROR | #+TBLFM: $3 =3D if($1 =3D string("") && $2 =3D string(""), string(""), $1= + $2) - input from range as vsum($1..$2): 1) empty -> 0, non-number -> 0: The format specifier is documented as =93N: interpret all fields as numbers, use 0 for non-numbers=94. Works as expected. | 1 | 0 | 1 | | 1 | | 1 | | 1 | text | 1 | #+TBLFM: $3 =3D vsum($1..$2); N 2) empty -> 0, keep other non-numbers: Without =93N=94 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 =3D vsum($1..$2) 3) numbers only: The format specifier is documented as =93E: keep empty fields in ranges=94. 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 =3D 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 =3D $1 + $2; N 2) empty -> 0, keep other non-numbers: Without =93N=94 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 =3D $1 + $2 3) numbers only: I suggest to extend the currently documented =93E: keep empty fields in ranges=94 to =93E: keep empty fields=94 or clearer =93E: keep empty fields as non-numbers=94 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 =3D $1 + $2; E Minor issue: I wonder what the entry =93vmean($2..$7);EN : Same, but treat empty fields as 0=94 in the manual means or is useful for. Michael