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. _______________________________________________ gnumeric-list mailing list [email protected] https://mail.gnome.org/mailman/listinfo/gnumeric-list
