Advanced filter or a formula to extract list

[Fazila]

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

 

[Kamolga]

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]

Thanks Kamolga will give that a try.

 

[SpillerBD]

AutoFilter, "TOP10" set for 15 doesn't get it?

 

[Fazila]

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