< and > than off of a filter function

[Lancer7]

Hello, I have a table built with the formula =FILTER(A3:I3000,ISNUMBER(SEARCH(Q3,B3:B3000)),""). This formula looks at A Column for a keyword in a big table and brings me back a smaller table based on that keyword. In this smaller chart there is one column of numbers and I am trying in another cell to make a formula =IF(AND(T5:T3000<=2000,T5:T3000>=1500),AH5:AH3000/AD5:3000,""). I understand that this formula might have multiple issues but I'm not a master and kind of just work through them until they work. But my problem right now is I believe its not reading the column T5:T3000 as a number. Even when I simplify the formula to IF(AND(T5<9999999),AH5/AD5,"") It comes back as false and leaves a blank cell even thou the number is only 1700. As far as I can see the cells are formatted as numbers so Im wondering if its not working because Im trying to pull a number off a =FILTER formula

 

[KRice]

The issue is that the AND will return only a single result, not an array.
Edit: I assume you meant to put "AD" in the last range reference...AD3000?
To check whether the values in T are numbers, enter =TYPE(T5) somewhere and let me know if the result is 1 (numeric) or 2 (text).

Try this:

Book1
	D	E															T										AD				AH
2
3	4
4	8.5
5
6																	1700										11				44
7																	1600										2				17
8
9
Sheet4

Cell Formulas
Range	Formula
D2:D2997	D2	=IF((T5:T3000<=2000)*(T5:T3000>=1500),AH5:AH3000/AD5:AD3000,"")
Dynamic array formulas.

 

[Peter_SSs]

Kirk has discussed one issue but I believe there is another based on your 'simplified' formula returning ""

As far as I can see the cells are formatted as numbers so Im wondering if its not working because Im trying to pull a number off a =FILTER formula

The problem is not related to the FILTER function.
The issue is that even though your cells might be formatted as number, if the underlying value is text it will remain that way and I think that is your issue. I think that the "numbers" in column T are actually text values that look like numbers.
In a blank cell put this formula =ISNUMBER(T5) and I suspect it will return FALSE.
Similarly try this modified 'simplified' formula and I think it will return a numerical value =IF(AND(--T5<9999999),AH5/AD5,"")

To consider the best way to deal with the issues it would be good to have some small sample (say 20 rows instead of 3000) of A:I data and your current FILTER formula so we can see just where those results are and tell us where you want the new formula. All that with IF(AND(T5:T3000<=2000,T5:T3000>=1500),AH5:AH3000/AD5:3000,"") would be ideal so we can copy to test with.


A couple of options to consider though would be whether to convert the relevant column in the original data from Text to Numeric or whether to handle the conversion in this final formula only - as I did with the simplified alternative above. Do you have a preference?

 

[Lancer7]

Kirk has discussed one issue but I believe there is another based on your 'simplified' formula returning ""

The problem is not related to the FILTER function.
The issue is that even though your cells might be formatted as number, if the underlying value is text it will remain that way and I think that is your issue. I think that the "numbers" in column T are actually text values that look like numbers.
In a blank cell put this formula =ISNUMBER(T5) and I suspect it will return FALSE.
Similarly try this modified 'simplified' formula and I think it will return a numerical value =IF(AND(--T5<9999999),AH5/AD5,"")

To consider the best way to deal with the issues it would be good to have some small sample (say 20 rows instead of 3000) of A:I data and your current FILTER formula so we can see just where those results are and tell us where you want the new formula. All that with IF(AND(T5:T3000<=2000,T5:T3000>=1500),AH5:AH3000/AD5:3000,"") would be ideal so we can copy to test with.


A couple of options to consider though would be whether to convert the relevant column in the original data from Text to Numeric or whether to handle the conversion in this final formula only - as I did with the simplified alternative above. Do you have a preference?

Hey thanks for the response! Much appreciated so you must be on the right track the first formula you suggested did in fact return FALSE, then the modified formula you suggested after did return a numerical value, I guess for my preference it would maybe be easier to convert the column of original data as I will be using it for many other tables as well. If you could walk me through doing that I would greatly appreciate it as in my eyes from the little I know I thought they were numbers and not text but was obviously incorrect

 

[Lancer7]

The issue is that the AND will return only a single result, not an array.
Edit: I assume you meant to put "AD" in the last range reference...AD3000?
To check whether the values in T are numbers, enter =TYPE(T5) somewhere and let me know if the result is 1 (numeric) or 2 (text).

Try this:

Book1
	D	E															T										AD				AH
2
3	4
4	8.5
5
6																	1700										11				44
7																	1600										2				17
8
9
Sheet4

Cell Formulas
Range	Formula
D2:D2997	D2	=IF((T5:T3000<=2000)*(T5:T3000>=1500),AH5:AH3000/AD5:AD3000,"")
Dynamic array formulas.

 

[Lancer7]

Hey thanks for the reply! It returned a 2 for text! And you are probably correct once I get the numbers/text thing figured out I wasn't sure if the formula would actually do what I needed with the arrays

 

[Lancer7]

The issue is that the AND will return only a single result, not an array.
Edit: I assume you meant to put "AD" in the last range reference...AD3000?
To check whether the values in T are numbers, enter =TYPE(T5) somewhere and let me know if the result is 1 (numeric) or 2 (text).

Try this:

Book1
	D	E															T										AD				AH
2
3	4
4	8.5
5
6																	1700										11				44
7																	1600										2				17
8
9
Sheet4

Cell Formulas
Range	Formula
D2:D2997	D2	=IF((T5:T3000<=2000)*(T5:T3000>=1500),AH5:AH3000/AD5:AD3000,"")
Dynamic array formulas.

And yes you are correct with the AD3000

 

[KRice]

The easy fix in the formula would convert the problematic T values to text, which you can do using the method shown by Peter. Here it is implemented in a mocked up snippet...I entered each apparent number in column T with a leading ` to force them to be text:

MrExcel_20240310.xlsx
	S	T	U	V								AD				AH
2
3
4	4
5	8.5
6		1700	2									11				44
7		1600	2									2				17
8
Sheet4

Cell Formulas
Range	Formula
S3:S2998	S3	=IF((--(T5:T3000)<=2000)*(--(T5:T3000)>=1500),AH5:AH3000/AD5:AD3000,"")
U6:U7	U6	=TYPE(T6)
Dynamic array formulas.

But if you want to fix the issue with the initial data, then as Peter has indicated, we'll need to see more about how the FILTER formula relates to the IF formula. Can you upload a sample showing where these formulas are and how they are related?

 

[Lancer7]

Hey thanks for the response! Much appreciated so you must be on the right track the first formula you suggested did in fact return FALSE, then the modified formula you suggested after did return a numerical value, I guess for my preference it would maybe be easier to convert the column of original data as I will be using it for many other tables as well. If you could walk me through doing that I would greatly appreciate it as in my eyes from the little I know I thought they were numbers and not text but was obviously incorrect

Kirk has discussed one issue but I believe there is another based on your 'simplified' formula returning ""

The problem is not related to the FILTER function.
The issue is that even though your cells might be formatted as number, if the underlying value is text it will remain that way and I think that is your issue. I think that the "numbers" in column T are actually text values that look like numbers.
In a blank cell put this formula =ISNUMBER(T5) and I suspect it will return FALSE.
Similarly try this modified 'simplified' formula and I think it will return a numerical value =IF(AND(--T5<9999999),AH5/AD5,"")

To consider the best way to deal with the issues it would be good to have some small sample (say 20 rows instead of 3000) of A:I data and your current FILTER formula so we can see just where those results are and tell us where you want the new formula. All that with IF(AND(T5:T3000<=2000,T5:T3000>=1500),AH5:AH3000/AD5:3000,"") would be ideal so we can copy to test with.


A couple of options to consider though would be whether to convert the relevant column in the original data from Text to Numeric or whether to handle the conversion in this final formula only - as I did with the simplified alternative above. Do you have a preference?

The easy fix in the formula would convert the problematic T values to text, which you can do using the method shown by Peter. Here it is implemented in a mocked up snippet...I entered each apparent number in column T with a leading ` to force them to be text:

MrExcel_20240310.xlsx
	S	T	U	V								AD				AH
2
3
4	4
5	8.5
6		1700	2									11				44
7		1600	2									2				17
8
Sheet4

Cell Formulas
Range	Formula
S3:S2998	S3	=IF((--(T5:T3000)<=2000)*(--(T5:T3000)>=1500),AH5:AH3000/AD5:AD3000,"")
U6:U7	U6	=TYPE(T6)
Dynamic array formulas.

But if you want to fix the issue with the initial data, then as Peter has indicated, we'll need to see more about how the FILTER formula relates to the IF formula. Can you upload a sample showing where these formulas are and how they are related?

 

[Lancer7]

So it might have a extra step in it but honestly, Im a farmer ha not a excel guy I just learn as I go. So it has two filter functions because I dont know how to put them together but one filter is in cell AA5 and it feeds from the filter function on Q5 which feeds from the "Main" chart. The numbers it pulls come from a formula using textjoin because the cells contained text and numbers and I wanted to somehow seperate the number from the text