Why does the composition of UNIQUE and FILTER distinguish between the actual number zero and the zero returned from empty cells?

user_14909621 0
Viewed 120

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

enter image description here

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:

enter image description here

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

excel-formula excel
2 Answers

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))

enter image description here

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.

enter image description here

If one has access to the insider's BETA-channel functionality, TOCOL()'s 2nd parameter can be used to "filter" out empty cells (and/or errors) in an intermediate step:

=UNIQUE(FILTER(TOCOL(A2:A3,1),TRUE))
user_48515900

Conspicuous by its absence from the OP is the formula in E2 below, which exhibits the same behaviour as that queried for D2: Screenshot showing insights yielded from VBA analysis

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).

user_188247270
Related