I would like to reference < or > signs in a filter formula,

my filter formula looks like this =filter(A:B, B:B<=0) which works great but I want to reference the condition to anoter cell e.i.

=filter(A:B, B:B=H1) H1="<=0"

However it says it doesn't find any matches in the filter so it is not picking up the condition. I am guessing it gets confused because of the double equal?

Spreadsheet here

CodePudding user response：

There is no built-in formula that lets you evaluate a custom string as formula.

In your use, B:B<=0 is not a string. It is actually evaluated into a (local) array of true/false.

If you want to change your formula based on the text of a cell with Apps Script, you can reference this question

That said, it seems as if you want to make a dropdown menu to toggle between two different analysis you are doing to your data set.

I can outline some steps to achieve the effect below:

1. Create 2 sets of arrays in helper columns. One set is B:B<=0; another is B:B>=0

2. Use indirect() to access that range by referencing a cell and use that as the input in filter().

3. Make a dropdown menu for that cell using the Data -> Data validation UI.

However, storing B:B<=0 is cumbersome. And thus it is not generally advisable.