Re: [office-formula] Section 4 Types: TBD: Should "blank" also bea possible value result?

["David A. Wheeler" <dwheeler@dwheeler.com>]

> > >An "empty cell" return value for example is needed
> > > in [HV]LOOKUP results, where for an empty cell the expression
> > > ISBLANK(LOOKUP(...)) must yield TRUE (OOo doesn't implement that yet).
> > 
> > I'm don't think that [HV]LOOKUP actually return empty cell in many implementations.
> > Should we spec it anyway, or leave that implementation-defined this go around?
> > I'm thinking of leaving that implementation-defined, I don't see lots of
> > consensus on this point.
> 
> People actually use it in their documents originating from "that other"
> application, we do have bug reports because we don't suppport it at the
> moment.. at least in the [HV]LOOKUP cases it really makes sense, any
> other functions?

Oh, sorry, I was unclear.  I think we MUST support [HV]LOOKUP.
The only question is, should we require anything if the lookup fails?
Ideally, we'd return an Error, but I believe that many don't do so;
the obvious solution is to leave allow an implementation-defined result
if the lookup fails (for this version of the spec).

> > > An "empty cell" value is undetermined until evaluated, which is needed
> > > in comparisons where with an empty cell A1 (or a LOOKUP result) both
> > > expressions IF(A1=0) and IF(A1="") yield TRUE.
> > 
> > Ahah!  That's right, that's a semantic difference, and that IS widespread
> > and NOT handled by the current spec.
> 
> However, most applications handle that correctly (at least OOo,
> Gnumeric, Excel), and to implement it does not necessarily need an
> EmptyCell return value. KSpread interprets this case as an empty string:
> with A1 empty =IF(A1=0;1;0) results in 0, =IF(A1="";1;0) in 1.

KSpread's approach is simple, but it seems wrong.  A spreadsheet
with many calculations might easily compare a number to an empty
cell, with the user expecting it to be a comparison to 0.
I'd propose adding an "Empty" type to represent the 'value' of an
empty cell, and handling it differently.  We need to deal with "Empty"
specially anyway, because many functions that use sequences ignore
empty cells... so we need to be clearer about what happens then.

KSpread folks: do you strongly object to that?  It'd be a minor
change to your implementation.  If you object, please explain...!

> > So we really have another type, which I suggest calling "Empty",
> > which is the type of an empty cell's contents.
> > In Convert to Number, if given a Reference to a cell with type Empty, the result is 0.
> > In Convert to Number, if given a Reference to a cell with type Empty, the result is
>                 ^^^^^^ copy&paste error, should be "String" instead.

Ooops.  That's right, I meant "String".

> > the empty string "".

Most functions that accept NumberSequence ignore empty values, but
I've been warned that some may not.  For the moment, I think we can
say "SHOULD" be ignored in those cases, and we'll see if we can promote
that to a strict requirement after some analysis.

--- David A. Wheeler