Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List top five results based on age grouping
Have a sheet called TOTALS set out as
A5 B6 C5 to V5 W5 X5 name club ind events total age group Fred aaaa 22 10 Anne bbbb 30 12 Tim aaaa 28 10 This sheet list everyone and every age group. On a new sheet need to list Top Five Point Scorers for each Age Group eg 10 yrs 1 Name Club Points 2 3 4 5 Prefer a function other than filter or pivot table as results are updated and automatically and will be used by others. Many thanks Loza |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List top five results based on age grouping
Have a look at the LARGE function on the points range using parameters
1, 2, 3, 4 and 5 for the top 5. You will also have to use MATCH and INDEX to get the name and the club, and you will probably have to deal with ties (as in the two 10s in your example). Hope this helps. Pete On Apr 10, 12:29*am, loza wrote: Have a sheet called TOTALS set out as A5 * * *B6 * * * *C5 to V5 * * * * *W5 * * * * * *X5 name *club * * *ind events * * * *total * * * * *age group Fred * *aaaa * * * * * * * * * * * * * 22 * * * * * *10 Anne * bbbb * * * * * * * * * * * * * 30 * * * * * *12 Tim * * aaaa * * * * * * * * * * * * * 28 * * * * * *10 This sheet list everyone and every age group. On a new sheet need to list Top Five Point Scorers for each Age Group eg * * * * * * * *10 yrs 1 * * Name * * Club * * *Points 2 3 4 5 Prefer a function other than filter or pivot table as results are updated and automatically and will be used by others. Many thanks Loza |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List top five results based on age grouping
My offer to you ..
a nice sample customized to suit your scenario (ready-to-use): http://www.freefilehosting.net/download/3f42h AutoList Top x by Age in separate shts.xls The steps: 1. Set-up the parent sheet In a sheet: x, You have your source data in cols A to X (as posted), data from row6 down, where the key cols are col W (points), and col X (age) List the ages in AA5 across, eg: 10, 11, 12, €¦ Then place in AA6: =IF($X6="","",IF($X6=AA$5,$W6-ROW()/10^10,"")) Copy AA6 across/fill down to cover the max expected extent of source data. This quickly creates the criteria table with tie-breakers which will be read in all the child sheets (the ones by age). 2. Set-up one child sheet In a new sheet: 10yrs Enter the age in B1: 10 In A3: =IF(B3="","",ROWS($1:1)) In B3: =IF(ROWS($1:1)COUNT(OFFSET(x!$Z$6:$Z$200,,MATCH($ B$1,x!$AA$5:$IV$5,0))),"",INDEX(x!A$6:A$200,MATCH( LARGE(OFFSET(x!$Z$6:$Z$200,,MATCH($B$1,x!$AA$5:$IV $5,0)),ROWS($1:1)),OFFSET(x!$Z$6:$Z$200,,MATCH($B$ 1,x!$AA$5:$IV$5,0)),0))) Copy B3 to D3. In D3, amend the INDEX to point to col W in x, make it: .... INDEX(x!W$6:W$200 ... Select A3:D3, copy down to say, D10. That should return the required auto-results for age: 10 in cols A to D. Read-off the top 5 as desired. Ties, if any, in the points will be displayed in the same relative order that they appear in the source sheet: x. Then to get the same for the rest of the age groups, just make copies of the sheet: 10yrs, key in the age in B1 (eg: 11), relabel the sheetname to taste. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "loza" wrote: Have a sheet called TOTALS set out as A5 B6 C5 to V5 W5 X5 name club ind events total age group Fred aaaa 22 10 Anne bbbb 30 12 Tim aaaa 28 10 This sheet list everyone and every age group. On a new sheet need to list Top Five Point Scorers for each Age Group eg 10 yrs 1 Name Club Points 2 3 4 5 Prefer a function other than filter or pivot table as results are updated and automatically and will be used by others. Many thanks Loza |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping rows based on header cells | Excel Discussion (Misc queries) | |||
Assigning a Value based on Results | Excel Worksheet Functions | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
Random Grouping/Fixture List?? | Excel Worksheet Functions | |||
Grouping the results of Networkdays | Excel Discussion (Misc queries) |