There are many situations where it is desirable to copy data from
one region to another on the spreadsheet. This is particularly
common when part of one sheet tracks part of another sheet.
AFAICT, copying is nontrivial when the source contains empty cells.
The simple "=" operator converts an empty cell into an explicit
numerical zero, which is very often not what is desired. In
particular, a great many functions including len(), count(),
average() etc. treat zero very differently from empty.
On possibility is to explicitly check if the source is empty, and
if so to set the destination to the null string.
This works for most purposes, although not for the type() function
and not for comparison to zero using (...=0) or (...<>0).
Question: Is there any way for a calculation to assign the "empty"
status to a cell?
Question: If not, is there a standard workaround? My practice
has been to use the null string as a substitute for empty, and
to never rely on the type() function or the zero-comparison
operators without defensively checking the count() beforehand.
Is there a better workaround? Is there a better way to think
about these concepts? Is this documented somewhere? In particular,
is there an authoritative list of which functions and operators
we have to be defensive about?
gnumeric-list mailing list
[hidden email] https://mail.gnome.org/mailman/listinfo/gnumeric-list
> 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
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.
We have the semantics of, say, 0+blank, from Excel. While we could
our own definition of what 0+blank should be, going that way is not
going to improve
interoperability. If you're going to break interoperability,
COPYVALUE is the cleaner
and more explicit way.
A quick google suggests that Excel has no copy-value formula. I
certainly know of
no such formula. It's no secret that the semantics of Excel formulas
are, shall we
say, not always well thought through. But it is what we have to play with.
Most vector operations ignore strings, booleans, and blanks. You're
better of building
a solution around that as it should come with the ability to share
formulas with others.
Actually, be wary of booleans if you need to interoperate with
LibreOffice. Last I checked
they did not have a concept of boolean values, only of numbers
formatted as booleans.