[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(A1) 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