Using Filter function to exclde values that are non-integer.

[nikolaki]

Hi,
I am trying to filter out values that are non-integers. I have a fairly complex filter function with multiple criteria.

I tried using:

filter(range, (criteria1, include 1 )*(int(a1:a1000)=a1:a1000). I get back #Value

That is, if a value in a1:a1000 is not an integer, I want to exclude that row from the results.

Any help is deeply appreciated

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this. I have included a check to also exclude any blank cells in the range.

23 10 15.xlsm
	A	B	C
1	2		2
2	89.23		-52
3	-52		0
4	0.1548		200
5	0		56
6	-1.66
7	200
8	56
9	1.47
10
Integers only

Cell Formulas
Range	Formula
C1:C5	C1	=FILTER(A1:A1000,(INT(A1:A1000)=A1:A1000)*(A1:A1000<>""))
Dynamic array formulas.

 

[nikolaki]

A1:A1000,(INT(A1:A1000)=A1:A1000)*(A1:A1000<>"")

Thank you so very much. This is the complete Filter function. I am still getting #VALUE#


=FILTER(Margin_Requirements_Details_202!A1:AW39000,
(Margin_Requirements_Details_202!B1:B39000>Sheet1!G3)*(Margin_Requirements_Details_202!C1:C39000="PM")*
(INT(Margin_Requirements_Details_202!G1:G39000)=Margin_Requirements_Details_202!G1:G39000)*
(Margin_Requirements_Details_202!G1:G39000<>""))`

If run without the BOLD, it works like a charm. Any ideas?

 

[nikolaki]

Sorry, The last ( ' ) at the end was a copy paste error. The function does not have that.

 

[Peter_SSs]

If run without the BOLD, it works like a charm. Any ideas?

Yes. I there are at least 2 causes of a #VALUE! error in that scenario.

1. There is at least one value in G1:G39000 of 'Margin_Requirements_Details_202' that is text, not numerical. In that sheet, what does this formula return if you put it in a vacant cell?=COUNTA(G1:G39000)-COUNT(G1:G39000)
   
   
2. There is at least one cell in G1:G39000 of 'Margin_Requirements_Details_202' that contains a #VALUE! error already.

 

[Alex Blakenburg]

Do you have a formula in Column G that returns "" ?
See if this works:
(added iferror around the int function)

=FILTER(Margin_Requirements_Details_202!A1:AW39000,
(Margin_Requirements_Details_202!B1:B39000>Sheet1!G3)*(Margin_Requirements_Details_202!C1:C39000="PM")*
(IFERROR(INT(Margin_Requirements_Details_202!G1:G39000),0)=Margin_Requirements_Details_202!G1:G39000)*
(Margin_Requirements_Details_202!G1:G39000<>""))

 

[nikolaki]

Yes. I there are at least 2 causes of a #VALUE! error in that scenario.

1. There is at least one value in G1:G39000 of 'Margin_Requirements_Details_202' that is text, not numerical. In that sheet, what does this formula return if you put it in a vacant cell?=COUNTA(G1:G39000)-COUNT(G1:G39000)
   
   
2. There is at least one cell in G1:G39000 of 'Margin_Requirements_Details_202' that contains a #VALUE! error already.

G1 was header text. changed in to G2 and it worked!

You are the best. I cant thank you enough

 

[Peter_SSs]

You're welcome. Glad it is sorted. Thanks for the follow-up.