Excel Match Query - Need someone with fab Excel skills!

[Redwinegirl]

Hi

I'm pretty new to using forums, so apologies if this isn't appropriate but I need techie help!

I have a spreadsheet showing student effort 'points' and now have used INDEX and MATCH so that I type in a number e.g. 1 and get the top performer....I have the top 5 and bottom 10. Simple...

Using:

=LARGE($O$2:$O$1378,J1383)

=INDEX($C$2:$C$1378,MATCH(O1383,$O$2:$O$1378,0))

 

I also have

=SUBTOTAL(1,O2:O1378)

so that when I filter the data (e.g. Pupil Premium) it gives me the average of the filtered data..

 

What I would like though, is to filter the data and the 'league' change to just show students that are filtered (e.g. Pupil premium). I don't want to create lots of sheets with filtered data in each (which would be easier in all honesty) BUT I would rather have it all together so the teachers just filter on their college, or gender and the league changes?

 

Hope that makes sense!

 

Help :-)

[featured_spectre]

Wouldn't the filter button do the job for you? Highlight your headings and click filter, and it should resolve that for you.

[CAM]

@featured_spectre: That won't work. The SUBTOTAL( ) function takes filtered cells into account, a normal function won't.

 

Example, using SUBTOTAL( ) to add up a range with hidden cells will show the total of visible cells only. A straight up SUM( ) will also include the hidden cells in it's calculation.

[featured_spectre]

ah ok. My mistake.

[Redwinegirl]

Thanks - Filtering doesn't work, I have used SUBTOTAL to calculate the average of the filtered data - this works fine.

It's the next stage of the league being updated that I am having problems with. I just want the

=LARGE($O$2:$O$1378,J1383)

To pull back the highest score for the filtered data, so this I assume needs a SUBTOTAL Command, but I don't want it to do a calculation e.g. Average, I want it to pull back the highest score?

Thanks!

[LosOjos]

You can use the SUBTOTAL to do this through a little trickery.

 

Next to your points column (or somewhere near) add a =SUBTOTAL(9,CELL) (i.e. if your points are in O2, it's be =SUBTOTAL(9,O2))

 

Now, shift your LARGE formula range to look at your new subtotals, and it'll work with filters. The trick is, because you're using SUBTOTAL on your points, when rows are hidden, SUBTOTAL becomes 0, taking it right to the bottom of the range LARGE looks at.

 

Hope that makes sense, I'll upload an example if not

[CAM]

Have you tried function 4 in SUBTOTAL( )? It's MAX( ) so it should pull out the largest number.

[Redwinegirl]

Oh Wow, This is FABULOUS! Thank you so much, works a treat!

This will save me loads of time.

THANK YOU SOOOOOO MUCH!!! @LosOjos

 

Kay!!!!