| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the top 10 from multiple groups.
I have spreadsheet similar to this:
Week 1 Week 2 Name Singles Handicap Singles Handicap % Grp 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Grp 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% I need to know which 5 people have the top percentage. This is scaled down version of the spreadsheet. There are a lot more groups and I need to keep the space between the groups for other data. There are also more weeks (10). I've tried lists but it won't let me use multiple selections. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the top 10 from multiple groups.
Hi,
Canoot make any sense from the data layout. Anyways, pivots have a feature for Sort and Top 10 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Selj" wrote in message ... I have spreadsheet similar to this: Week 1 Week 2 Name Singles Handicap Singles Handicap % Grp 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Grp 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% I need to know which 5 people have the top percentage. This is scaled down version of the spreadsheet. There are a lot more groups and I need to keep the space between the groups for other data. There are also more weeks (10). I've tried lists but it won't let me use multiple selections. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the top 10 from multiple groups.
Hi,
Sorry, I guess the spacing all disappeared when I posted it. Not exactly straight, but it might be easier to under stand... Week 1 Week 2 Name Singles Handicap Singles Handicap % Group 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Group 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% In the meantime I'll look into Pivots. "Ashish Mathur" wrote: Hi, Canoot make any sense from the data layout. Anyways, pivots have a feature for Sort and Top 10 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Selj" wrote in message ... I have spreadsheet similar to this: Week 1 Week 2 Name Singles Handicap Singles Handicap % Grp 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Grp 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% I need to know which 5 people have the top percentage. This is scaled down version of the spreadsheet. There are a lot more groups and I need to keep the space between the groups for other data. There are also more weeks (10). I've tried lists but it won't let me use multiple selections. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the top 10 from multiple groups.
If you want top 5 total with ties (Not top 5 by group), highlight all columns
and place a Filter on. (Data FilterAutofilter). Select (Top 10...) on the % column and select top 5 percent. This can return more than 5 rows if you have multiple people with the same %. Another option is to use a column to right, using =RANK(), if you'd like to break out by group. Something like =RANK(F2,$F$2:$F$20) and drag down thr group. Then, you can view only those with ranks up to 5 using a filter. "Selj" wrote: Hi, Sorry, I guess the spacing all disappeared when I posted it. Not exactly straight, but it might be easier to under stand... Week 1 Week 2 Name Singles Handicap Singles Handicap % Group 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Group 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% In the meantime I'll look into Pivots. "Ashish Mathur" wrote: Hi, Canoot make any sense from the data layout. Anyways, pivots have a feature for Sort and Top 10 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Selj" wrote in message ... I have spreadsheet similar to this: Week 1 Week 2 Name Singles Handicap Singles Handicap % Grp 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Grp 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% I need to know which 5 people have the top percentage. This is scaled down version of the spreadsheet. There are a lot more groups and I need to keep the space between the groups for other data. There are also more weeks (10). I've tried lists but it won't let me use multiple selections. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the top 10 from multiple groups.
I can't just select the whole column. There is actually data in between the
groups that would skew the results. So, I need to select the % in the first group then press control and select the second group and so on. When I try to use a Filter it says can't filter data from multiple groups. When I try and use Rank, it says too many arguements. "Sean Timmons" wrote: If you want top 5 total with ties (Not top 5 by group), highlight all columns and place a Filter on. (Data FilterAutofilter). Select (Top 10...) on the % column and select top 5 percent. This can return more than 5 rows if you have multiple people with the same %. Another option is to use a column to right, using =RANK(), if you'd like to break out by group. Something like =RANK(F2,$F$2:$F$20) and drag down thr group. Then, you can view only those with ranks up to 5 using a filter. "Selj" wrote: Hi, Sorry, I guess the spacing all disappeared when I posted it. Not exactly straight, but it might be easier to under stand... Week 1 Week 2 Name Singles Handicap Singles Handicap % Group 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Group 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% In the meantime I'll look into Pivots. "Ashish Mathur" wrote: Hi, Canoot make any sense from the data layout. Anyways, pivots have a feature for Sort and Top 10 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Selj" wrote in message ... I have spreadsheet similar to this: Week 1 Week 2 Name Singles Handicap Singles Handicap % Grp 1 Dave 23 23 17 18 81.0% Jim 25 25 21 22 93.0% Brad 14 12 18 18 62.0% Moritz 20 13 22 21 76.0% Bob 25 24 22 19 90.0% Grp 2 Gary 16 19 23 23 81.0% Tony 17 19 25 24 85.0% Gary 19 19 14 12 64.0% Frank 23 20 20 13 76.0% Mike 22 19 25 24 90.0% I need to know which 5 people have the top percentage. This is scaled down version of the spreadsheet. There are a lot more groups and I need to keep the space between the groups for other data. There are also more weeks (10). I've tried lists but it won't let me use multiple selections. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the top 10 from multiple groups.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating stacked bar for multiple groups and multiple years | Charts and Charting in Excel | |||
| Multiple Criteria to Classify groups | Excel Worksheet Functions | |||
| How to find a third true-false relationship between three groups ofvariables knowing two | Excel Worksheet Functions | |||
| How to find the right discussion groups for Microsoft Support? | Excel Discussion (Misc queries) | |||
| find the max values for cells in consecutive groups of 600 | Excel Discussion (Misc queries) | |||
Hybrid Mode
