Listing top three leaders

[Guest]

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

 

[Biff]

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

 

[Guest]

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]

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

 

[Guest]

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 Stats.zip

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

 

[T. Valko]

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

 

[Guest]

Thanks for your help

I gave what you have suggested a try and it seems it did nothing is there
any reason why this would not be working for me...

(F2, CTRL,SHIFT,ENTER)

I gave it numerous tries with no luck...

Not sure of the size of the file but it’s not over 4 Megs you’re welcome to
send it to my email at (e-mail address removed)

Thanks for the help

 

[T. Valko]

Ok, sent an email with the corrected file attached.

Biff

 

[jlepack]

You have to press F2 and then press Ctrl+Shift+Enter all at the same
time. (Sort of like Ctrl+Alt+Del)

 

[T. Valko]

I got a bounce-back on the email.

Biff

Ok, sent an email with the corrected file attached.

Biff