Forum Discussion

RFreund's avatar
RFreund
Copper Contributor
Jan 31, 2020

Filter Function with numbers and empty cells

I'm puzzled by how excel I handling the following situation. I have a column of numbers and empty cells (not blank cells, they are filled with "" - side question what is the correct term for such a c...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 31, 2020

RFreund 

You may add one more condition

=FILTER(A1:A21,(A1:A21>F9)*(A1:A21<>""),"No Outlier")

Without this it calculates empty cells since any text, even empty string, is more than any number.

  • RFreund's avatar
    RFreund
    Copper Contributor
    Jan 31, 2020

    SergeiBaklanThank you. For some reason I failed to realize "any text, even empty string, is more than any number". 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 31, 2020

      RFreund 

      Try

      ="abc">1

      it returns TRUE 

Resources