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

[Michael Brand <michael.ch.brand@gmail.com>]

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