Fix =Z1 when Z1 = "" should return "" not 0, No work around w/ Dynamic Arrays
There has been a longstanding issue as follows. When referencing a cell (e.g. formula in cell A1 is "=Z1") and Z1 is blank/"" then the value returned in A1 is 0 (not blank/"" as expected). The work around for this has always been to change the formula to =Z1&"".
The problem is now with Dynamic arrays this is not possible. Create a table with 3 columns, first 2 columns are a text values and 3rd column is a date or numeric. The write a dynamic array function =FILTER(Table1,Column2<>"Y"). The spilled array will return a 0 in column 1 or 2 where the value was blank/"". And if you change the formula to =FILTER(Table1,Column2<>"Y")&"" then it will cause a problem with numeric columns (e.g. date values).
Please vote so we can get this fixed.
Michael Daughety commented
There is a worksheet function ISBLANK that returns true/false. Maybe you can modify your formula to use it and not depend on "" or 0 values.
But despite that, I completely agree with your reques.
A.C. WILSON commented
This problem is due in part to the more general shortcoming within Excel that a NULL value, and a cell containg a NULL value, which have no data type (neither numeric, logical, nor text), are neither well defined nor documented/explained. (NULL is not the same as a string of zero length ( ="" ); NULL is not the same as zero.)
Access handles this slightly better, in that in Access, but not in Excel, one can set a cell to have the NULL value, using a formula.