Re: copying empty cells, or not

[John Denker <jsd av8n com>]

On 6/3/20 7:09 PM, Morten Welinder wrote:

> You are better off creating a new function, say COPYVALUE, in fn-info
> to do the copying.

But doesn't that guarantee that my spreadsheets will be totally
un-portable?

I share spreadsheets with people, very few of whom are in a position
to compile their own private versions.

Given the choice between a private custom function and the null-string
approach, I prefer the latter.

Tangential remark: In addition to the comparison operators mentioned
previously, plain old arithmetic operators such as "+" treat an empty
cell different from a null string.

Further remark: For many purposes, such as representing missing data
in a sequence, a non-null string such as "xx" serves the purpose.
It even has some /advantages/ over the null string, and even over
the empty value.  Also some disadvantages.  An easy way to detect
the out-of-band value is via the count() function.

There's a related problem for which I have no reasonable workaround,
namely this:  average(a1:a10) is not necessarily the same as
average(0+a1:a10).  This is highly counterintuitive, and would seem
to violate the axioms that define what we mean by "+" and "0".  In
particular, if there is a missing value represented by an empty cell,
adding zero silently gives the wrong answer.  Meanwhile, if the missing
value is represented by a string (null or "xx"), adding zero throws an
error which the average() function cannot handle.  One could imagine
a #ignore! value with the property that 0+#ignore! = #ignore, and which
would be tolerated and ignored by vector-oriented functions such as
average().  That would be useful, but it would be super-incompatible,
as well as hard to implement.

Overall, one gets the impression that the excel data types rest on a
rather shaky conceptual foundation.  Don't get me started on complex
numbers.