1. =RANK(\$H4,\$H\$4:\$H\$346) gives me the rank of the entire list. My list has 4 'types in column B. How can I rank each individual type?

2. How should types be ranked? By the sum of the values in column H, or the maximum, or the average, or ...?

3. Reading my question back, I was not very clear:

I have a list of players with individual scores. Column B denotes their position, GK, DEF, MID, STR. As well as ranking the entire player list, I would like to rank each striker against the list of strikers, etc.

4. Enter the following formula in row 4 in the within-group rank column:

=SUMPRODUCT((B4=\$B\$4:\$B\$346)*(H4<\$H\$4:\$H\$346))+1

and fill down to row 346.

This assumes that the highest score in column H should have rank 1. If the lowest score should have rank 1, change < to > in the formula.

5. [quote name='HansV' post='791295' date='29-Aug-2009 16:38']=SUMPRODUCT((B4=\$B\$4:\$B\$346)*(H4<\$H\$4:\$H\$346))+1[/quote]

Thanks Hans.

I have done this in 4 separate columns and added an IF statement, so that I can filter each individual group to Top 10.... (Or strangely bottom 10).

=IF(\$B5<>"STR","",SUMPRODUCT((\$B5=\$B\$5:\$B\$347)*(\$H 5<\$H\$5:\$H\$347))+1)

I think this works....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•