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:

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

2 answers

  • answered 2022-05-06 19:45 Scott Craner

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

  • answered 2022-05-06 21:22 Spectral Instance

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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum