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

[Eike Rathke <erack@sun.com>]

Hi David,

On Friday, 2006-11-17 14:02:29 -0500, David A. Wheeler wrote:

> >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?


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


> 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.
> the empty string "".


  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.