Dynamic filter help.

[russelldt]

I keep getting a Div/0 error with this formula

=FILTER('Job Register'!A:AT,(ISNUMBER('Job Register'!AT:AT))*(('Job Register'!AT:AT<-0.02)+('Job Register'!AT:AT>0.02)))


My column AT has both blank cells and #div/0! cells.

Any help would be appreciated, Thanks

 

[awoohaw]

Maybe change the ISNUMBER function to an ISERROR function: not sure if i have the parenthesis correct)

=FILTER(AT:AT,(ISERROR(AT:AT)=FALSE)*(IFERROR((AT:AT<-0.02),0)+IFERROR((AT:AT>0.02),0)))

subsequent edit. I don't think the isnumber is needed, unless you have other data that needs to be filtered:

=FILTER(AT:AT,(IFERROR((AT:AT<-0.02),0)+IFERROR((AT:AT>0.02),0)))

 

[russelldt]

Maybe change the ISNUMBER function to an ISERROR function: not sure if i have the parenthesis correct)

=FILTER(AT:AT,(ISERROR(AT:AT)=FALSE)*(IFERROR((AT:AT<-0.02),0)+IFERROR((AT:AT>0.02),0)))

subsequent edit. I don't think the isnumber is needed, unless you have other data that needs to be filtered:

=FILTER(AT:AT,(IFERROR((AT:AT<-0.02),0)+IFERROR((AT:AT>0.02),0)))

Thanks for this.

I now get a #calc! error. I have tried both your proposed solutions