OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

Eike Rathke looked at the TBD text:
> | TBD: Should "blank" also be a possible value result? This is needed for
> | empty parameters; it can also occur with an intersection of
> | non-overlapping references. For the moment, presume that it's a kind
> | of reference (a reference with no cells as content). But are they
> | distinguishable?  And what is the type of an empty cell?  Empty is
> | distinguishable from a zero-length Text value, because COUNTA makes this
> | distinction.

and then sagely commented:
> Actually we need both and have to distinguish between an "empty cell"
> and a "no cell" as in the intersection case....
> A "no cell" intersection is a reference error.

You're right, and that's a no-brainer.  Silly me. I'll fix that text to make it clear
that an intersection of non-overlapping references produces a value of type Error,
and clearly Error is NOT a new type :-).

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

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

So we really have another type, which I suggest calling "Empty",
which is the type of an empty cell's contents.  Empty is a singleton type,
with only one instance value.  In most cases this is the same as
numeric, indeed, TYPE(Ref) where Ref refers to an empty cell will return
the same thing as when Ref refers to a cell with a number in it.
That's at least true in Excel and OpenOffice.org, and I suspect all spreadsheets.
BUT... A1=0 and A1="" are both TRUE if A1 is empty, because the
"Empty" type promotes to 0 in Number context, and the empty string ""
in Text context.  And again, true for both Excel and OpenOffice.org.

I don't know if all implementations do things this way... but I bet many do.
The fact that BOTH Excel and OpenOffice.org do this is really good evidence
to me that this IS an agreed-on semantic.
I think we should spec this behavior, it's the sort of stuff people depend on.
And we need a new type (Empty) to capture these semantics.

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

> An empty cell passed as parameter will never be the same as an omitted
> parameter, at least to my knowledge. Any other observations?

I think that's right, although for an interesting reason - you cannot pass
empty cells, you can only pass REFERENCES to empty cells. And since they have
type Reference, there's not an issue. E.G., for:
SIN receives a REFERENCE to A1... it really gets something of type Reference.
Since SIN expects a Number, it'll use the "Convert to Number" routine, and all's well.

An empty parameter could itself have the type "Empty", and still be
distinguishable from a reference-to-an-empty-cell.  I don't think we should
actually SPECIFY that behavior now.  Unless we specify a way to create
user-defined functions, I don't see any need to specify the type of an
empty parameter.  And since user-defined functions aren't in this version,
no point.  No point in overspecifying - let's
specify only what is actually needed, and be happy.  We risk getting it
wrong otherwise.

--- David A. Wheeler

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]