| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing top three leaders
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
Ok, sent an email with the corrected file attached.
Biff "Killer" wrote in message ... 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 Thanks for the help "T. Valko" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
You have to press F2 and then press Ctrl+Shift+Enter all at the same
time. (Sort of like Ctrl+Alt+Del) T. Valko wrote: Ok, sent an email with the corrected file attached. Biff "Killer" wrote in message ... 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 Thanks for the help "T. Valko" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
I got a bounce-back on the email.
Biff "T. Valko" wrote in message ... Ok, sent an email with the corrected file attached. Biff "Killer" wrote in message ... 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 Thanks for the help "T. Valko" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Listing top three leaders
I wonder why... How big is the file?
Because email you should be able to send upwards of 4 Megs. **** I see the problem wrong email address posted. Its Sorry! Yea I tried F2 then CTRL,SHIFT,ENTER at the same time with no luck.... "T. Valko" wrote: I got a bounce-back on the email. Biff "T. Valko" wrote in message ... Ok, sent an email with the corrected file attached. Biff "Killer" wrote in message ... 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 Thanks for the help "T. Valko" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reading Multiple Pages Listing Top three Results. | Excel Worksheet Functions | |||
| listing winners formula | Excel Discussion (Misc queries) | |||
| Preventing a listing of duplicate records | Excel Discussion (Misc queries) | |||
| How to find duplicate data in two tables,then listing it?(Excel) | Excel Worksheet Functions | |||
| Periodically listing files in a folder | Excel Discussion (Misc queries) | |||
Linear Mode
