0

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

Mee
  • 133
  • 1
  • 8
  • 1
    It's unclear to me what you're attempting to do. Are you trying to check if a cell in column B contains the text value `<=0` or check whether a cell in column B is negative? Your example sheet is confusing because column B seems to contain data that fits neither. – Sebastian Smiley Jul 13 '22 at 06:50
  • I originally was trying to add a condition that would take all empty as well as all cells with content as a condition. My condititons changes dependent on other parameters so it could be tha the condition =3 or ='all' and that was what I was trying to do here in a around the way. – Mee Jul 13 '22 at 08:25

1 Answers1

1

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.

Argyll
  • 8,591
  • 4
  • 25
  • 46
  • Okay thank you. The reason why I was trying to do it was because the condition changes dependent on other parameters I have in my sheet so I was trying to see if I could do it in a cleaver way. The original reason I wanted to do it was because I needed a formula that would include both cells that are empty as well as cells with content as a condition. – Mee Jul 13 '22 at 08:23
  • @Mee: For your current question, there is not much else to comment on. For other aspects of your application, feel free to ask more questions. Just be very precise in what you are looking for. – Argyll Jul 13 '22 at 08:42