Advanced Filter returning complete database not filtered results

[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

 

[Jarek Kujawa]

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

 

[DianeG]

Thanks for the response, this example was on the contextures site and I just
can't get it to work

 

[Jarek Kujawa]

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

 

[bpeltzer]

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)