Filtering and ranking in Excel
 

I can add and use auto filter(!!) and I can use a formula to determine the ranked position, but can I do them both together, so when filtered, only the remaining entries are ranked as though the non filtered options do not exist? Thought I may be able to do it using the subtotal function, but cannot get anything working using rank as well.

Any help much appreciated.

Many thanks


Abs

Re: Filtering and ranking in Excel
 

Excuse me?

Do you mean: you only want to list the top 5? Or, you only want to find the total of the top 5? Or what...?

Re: Filtering and ranking in Excel
 

Sorry, no. I'll try to give an example.

NAME SCORE RANK
Ann 100 1
Dave 95 2
Ann 45 5
Steve 65 4
Dave 40 6
Ann 89 3
Ann 15 7

Columns A,B and C as above, with C auto completing with a RANK formula. If I then filter column A to just Ann, it will show her ranks as 1, 5, 7 and 3. What I want it to do, is just rank Ann and ignore the rest, so it ranks 1, 2, 3, 4. If that makes sense!!???


Many thanks

Abs

---------- Post added at 15:23 ---------- Previous post was at 15:21 ----------

Not only can I not explain what I mean, I cannot get the formatting right either!!!

Abs

Re: Filtering and ranking in Excel
 

Hmm...

There are some useful tips at the bottom of this page, together with a workbook that you can download (but I'm not sure this is what you want):

http://www.cpearson.com/excel/Rank.aspx

Re: Filtering and ranking in Excel
 

Thanks TheNorm, I had found that site whilst Googling, it does not show what I want though. Perhaps it's just not possible in Excel!! I will keep looking though.

Many thanks

Abs