Advanced filter or a formula to extract list

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
Hi

I'm trying to put together a list of students with the worst punctuality. I couldn't find a formula so I thought I would try advanced filter. This worked great for values below 90, however, when I try to extract the top 15 with the worst punctuality the filter doesn't seem to happen. The formula I am using is:

=IF(Year="Year 11",Sheet3!H2>LARGE(Sheet3!H2:H1401,15))

I need to filter out so it only shows students in Year 11

I have also tried:

=Sheet3!H2>LARGE(Sheet3!H2:H1401,15)

To see if it just filters out the top 15 but still not getting anywhere

Any thoughts?

Thanks
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
Hi,

I would usualy use rank to get position with countif to avoid duplicates: if data is 5,4,4,3, rank says 1,2,2,4.

[/URL][/IMG]
 

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
It does and that is what I ended up doing but I had to do the same for multiple sheets and I'm lazy so I thought if I could get the advanced filter to work it could save me having to copy and paste :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,929
Messages
5,447,361
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top