Why does the composition of UNIQUE and FILTER distinguish between the actual number zero and the zer-CodePudding

Suppose we have 2 cells, one contains zero and the other is empty, see the range A2:A3 in the figure:

If they pass a FILTER we get two zeros as a result, see the output of FILTER(A2:A3, TRUE) in B2:B3, presented in the next figure. When we apply UNIQUE to the range where the previous result was stored, the output is the alone 0 (see the result in C2). But a composition UNIQUE(FILTER(...)) does not treat these zeros as equal to each other, see the output of UNIQUE(FILTER(A2:A3,TRUE)) in D2:D3:

Why is that? Can we suppress this behavior and get only one zero as a result of the composition?

p.s. I work with Office 365, Excel Version 2108

CodePudding user response：

This is an on going issue that Microsoft appears to have chosen to continue. That is that it treats truly blank cells as 0. And as such any formula that returns that value will return 0. INDEX, (*)LOOKUP, or simple mathematical functions all do it.

And this appears to happen at the last step, as you see with the final formula you tried. The FILTER obviously returned a blank to the Unique but when it inserted the value into the worksheet it returned 0 for the blank.

One method to counter this is to concatenate an empty string on the back end then try to turn number back to numbers:

=LET(rng,A2:A3,uq,UNIQUE(FILTER(rng,TRUE)&""),IFERROR(--uq,uq))

Another option would be to test the range as it is being filtered and replace blanks with an empty string:

=LET(rng,A2:A3,UNIQUE(FILTER(IF(rng="","",rng),TRUE)))

Which is shorter and will maintain the data type.

If one has access to the insider's BETA-channel functionality,

VBA yields an insight into Excel's calculation engine, as it always distinguishes between an empty cell, and a literal 0: if the engine reads 2 data types then, in this context, it's not going to return less than 2 results even if, 'by Microsoft convention' they are both returned as 0 (the formula in C2 is referencing a range that the engine sees as only containing a single data type (cf. hash variable), so returns just a single 0; the formula in F2 shows that the only apparent way to get Excel to return a truly blank cell is to pass in an empty string as part of an array constant).