Filter Function with numbers and empty cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1142929%22%20slang%3D%22en-US%22%3EFilter%20Function%20with%20numbers%20and%20empty%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142929%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20puzzled%20by%20how%20excel%20I%20handling%20the%20following%20situation.%20I%20have%20a%20column%20of%20numbers%20and%20empty%20cells%20(not%20blank%20cells%2C%20they%20are%20filled%20with%20%22%22%20-%20side%20question%20what%20is%20the%20correct%20term%20for%20such%20a%20cell%3F).%20When%20I%20use%20the%20filter%20function%20to%20return%20cells%20that%20are%20less%20than%20a%20given%20value%2C%20the%20%22if_empty%22%20value%20is%20returned%20if%20no%20values%20are%20less%20than%20the%20given%20value.%20However%2C%20if%20I%20use%20greater%20than%2C%20the%20%22if_empty%22%20value%20is%20not%20returned.%20See%20attached%20example.%26nbsp%3B%3CBR%20%2F%3EWhy%20is%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1142929%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142953%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20with%20numbers%20and%20empty%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F540145%22%20target%3D%22_blank%22%3E%40RFreund%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20one%20more%20condition%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER(A1%3AA21%2C(A1%3AA21%26gt%3BF9)*(A1%3AA21%26lt%3B%26gt%3B%22%22)%2C%22No%20Outlier%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWithout%20this%20it%20calculates%20empty%20cells%20since%20any%20text%2C%20even%20empty%20string%2C%20is%20more%20than%20any%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142980%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20with%20numbers%20and%20empty%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you.%20For%20some%20reason%20I%20failed%20to%20realize%20%22%3CSPAN%3Eany%20text%2C%20even%20empty%20string%2C%20is%20more%20than%20any%20number%3C%2FSPAN%3E%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1143844%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20with%20numbers%20and%20empty%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1143844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F540145%22%20target%3D%22_blank%22%3E%40RFreund%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%22abc%22%26gt%3B1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20returns%20TRUE%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 cell?). When I use the filter function to return cells that are less than a given value, the "if_empty" value is returned if no values are less than the given value. However, if I use greater than, the "if_empty" value is not returned. See attached example. 
Why is this?

3 Replies

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

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

@RFreund 

Try

="abc">1

it returns TRUE