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

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

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

Conspicuous by its absence from the OP is the formula in E2 below, which exhibits the same behaviour as that queried for D2: 