Ok after playing around with it for many hours I'm now getting this #Value!
Error in every column other than the points.

I have added the coding and made the adjustments with no luck...

Here's an example of my spreadsheet.

http://www.nr1hockey.com/downloads/POW%20Stats.zip

It's driving me crazy why it won't work....




"Biff" wrote:

Ok....

Assume your data is in the range A9:F12 (like your posted example)

Enter the number of the top n that you want in some cell, say, H9. If you
want a top 3 enter 3 in H9. If you want a top 5 enter 5 in H9. If you want a
top 10 enter 10 in H9.

Enter this formula in cell I9. This formula counts how many entries will
meet the criteria. If there are ties that meet the criteria your top 3 may
be more than 3. It could be 4 or 5 or ?????

=COUNTIF(F9:F12,"="&LARGE(F9:F12,H9))

Now, extract the data that meets the criteria.

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER) in, say, A2:

=IF($F2<"",INDEX(A$9:A$12,SMALL(IF($F$9:$F$12=$F2 ,ROW(A$9:A$12)-ROW(A$9)+1),COUNTIF($F$2:$F2,$F2))),"")

You may initially see either an error or a blank cell when you enter that
formula. Don't worry about it! It'll be fine once all the formulas are
entered.

Copy the formula in A2 across to E2.

Enter this formula in F2:

=IF(ROWS($1:1)<=$I$9,LARGE(F$9:F$12,ROWS($1:1)),"" )

Once the formula in F2 is entered all the other formulas will show results.

Now, select A2:F2 and copy down until you get blanks.

Here's a sample file:

top n.xls 16kb

http://cjoint.com/?lyeIOrDJas

If you download the sample file play around with it. Change the points to
include ties and see what happens. Try changing the top n number. Check it
out!

Biff

"Killer" wrote in message
...
Well I gave it try and it only showed one player and one stat not sure how
you say it shows the top 5.

I would need it to show the top 3 players in points and the 8 columns of
stats. Player first & last name are in two columns with the rest listed
below
as one column each.


Example:
Pos Team Goals Assists +/- Points
1. Joe Sakic LW BUF 3 5 9 17
2. Ron Wilson D CGY 5 5 4 14
3. Don Williams RW SJ 4 4 4 12

Let me put out I don't know too much about formulas... so any help would
be
appreciated.



"Biff" wrote:

See this:

http://tinyurl.com/yfd5t9

It's for a top 5 but you should be able to adapt it easily for your
needs.

Biff

"Killer" wrote in message
...
I have worksheet that has over 2000 players name and stats... What I
want
it
to list the top thee point leaders by there stats in separate column.

A B Ga Assists +/- Ponits
9 Name 1 Last Name 1 7 6 7 20
10 Name 2 Last Name 2 9 10 7 26
11 Name 3 Last Name 3 7 14 10 31
12 Name 4 Last Name 4 42 12 4 56

The resulte would be as followed:
1. Name 4 Last Name 4 42 12 4 56
2. Name 3 Last Name 3 7 14 10 31
3. Name 2 Last Name 2 9 10 7 26

thanks ahead