# Help with Filter Formula

Copper Contributor

# Help with Filter Formula

Hello Microsoft Excel Community,

I'm having trouble making a filter formula work. Perhaps someone can help me out.

I'm trying to filter a list of job numbers if the quantity is +/- 5% from the base amount.

I have tried the following formulas with no success:

FILTER(A6:A25,\$B\$6:\$B\$25=MEDIAN((B2*0.9),(B2*1.1))) Returns only "DE-1"
FILTER(A6:A25,AND(B6:B25>B2*1-B3),B6:B25<B2*1+B3) Returns only "TRUE", FALSE"
FILTER(A6:A25,(1-ABS(B6:B25/(B2<B2*(1+B3))))) Returns all Job Numbers.

A screenshot is provided below of how my data is set up.

Any help would be greatly appreciated.

Thank you.

3 Replies

# Re: Help with Filter Formula

@CM1927 Something along these lines should do the trick:

=LET(
base, B2, pct, B3, jobs, A6:A25, qty, B6:B25,
lower, base*(1-pct),
upper, base*(1+pct),
FILTER(jobs, (qty>=lower)*(qty<=upper), "none")
)

Written without the LET function would look like this:

=FILTER(A6:A25, (B6:B25>=B2*(1-B3))*(B6:B25<=B2*(1+B3)), "none")

See attached...

# Re: Help with Filter Formula

Both of these options worked really well.

Thank you very much.

# Re: Help with Filter Formula

filter(a6:a25,abs(b6:b25-b2)<=b2*b3,"")