Jan 31 2020 06:33 AM
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?
Jan 31 2020 06:38 AM
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.
Jan 31 2020 06:43 AM
@Sergei BaklanThank you. For some reason I failed to realize "any text, even empty string, is more than any number".
Jan 31 2020 07:45 AM