Advanced Filter returning complete database not filtered results
 

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

Advanced Filter returning complete database not filtered results
 

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


On 12 Gru, 14:46, DianeG wrote:
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

Advanced Filter returning complete database not filtered results
 

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

"DianeG" wrote:

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

Advanced Filter returning complete database not filtered results
 

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

On 12 Gru, 15:20, DianeG wrote:
Thanks for the response, this example was on the contextures site and I just
can't get it to work



"DianeG" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Advanced Filter returning complete database not filtered results
 

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)

"DianeG" wrote:

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