Forum Discussion

CM1927's avatar
CM1927
Copper Contributor
Mar 11, 2024

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.

 

 

 

Formulas and Functions
  • djclements's avatar
    djclements
    Bronze Contributor
    Mar 11, 2024

    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...

Resources