Advanced Filter returning complete database not filtered results

  • Thread starter Thread starter DianeG
  • Start date Start date
D

DianeG

My sample database in in the range a1:d8
A B C D
Date Customer Product Total
01/01/2002 MegaMart Cookies $278.07
03/01/2002 MiniMart Bread $789.00
03/01/2002 SuperMart Produce $1,365.35

My criteria is in F2 and I'm trying to filter a list of items greater than
the top 5 items. The criteria looks like this:
=D2>=LARGE($D$2:$D$28,5)

The cell that contains the criteria formula is showing false because D2 is
lower than the top 5 amounts

Whatever I try, I keep getting the complete database instead of the results,
I should get 5 or so records.

I know that you can do this with conditional formatting but I'm trying out
different filtering options.

Can anybody figure out what I've done wrong

Thanks in advance

Diane
 
maybe you need to look for a list of items LESS than than the top 5
items?

If I don't miss anything...

there shouldn't be anything LARGER than than the 5 top items => thus
Excel might be right
 
Thanks for the response, this example was on the contextures site and I just
can't get it to work
 
only now I have notice that the =D2>=LARGE($D$2:$D$28,5) formula
looks for values greater or equal to the 5TH largest item in the range
 
You didn't state it but I assume that F1 contains the word Total, and that
your criteria range for the advanced filter is F1:F2. Then what should
appear in F2 is something like >=40. To get that, the formula in F2 would be
=">=" & large($D$2:$D$28,5)
 
Back
Top