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

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

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

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

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

Ok......

I downloaded the file and fixed it. It's too big to upload to a free file
share site, so......follow these instructions:

On Sheet p select cell B6
Press function key F2
Now, hold down both the CTRL key and the SHIFT then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly braces { }.
You'll see these braces in the formula bar. You can not just type these
braces in. You MUST use the key combination of CTRL,SHIFT,ENTER.

Once you have the formula properly entered in B6, drag copy that formula
across to C6 then down to C8. There was a slight error in your formula in
column C.

Now, select cell D6 and repeat the above (F2, CTRL,SHIFT,ENTER)

Drag copy that formula across to F6 then down to F8.

Anytime you edit or change those formulas you MUST re-enter them with the
key combination of CTRL,SHIFT,ENTER.

Biff

"Killer" wrote in message
...
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