Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use SMALL with certain restrictions?
I'm trying to generate a Top 10 times list for each grade level (fr, so, jr,
sr) from a spreadsheet containing several years worth of race times. The data are arranged chronologically. Any ideas how I could make a Top 10 Freshman time list? I also want to make a top 10 list for all times, with the caveat that once a runner achieved a top 10 time they could not hold any more of the top 10 (for example the runner with the fastest time also has #s 3,4,6, and 8. Thanks! Below is an excerpt of how the data are arranged. B C D 3 Ben Bach JR 33:20.00 4 Stephano Kaiser SO 37:16.00 5 Larry Stange JR 48:03.00 6 Chad Schmidt FR 29:37.00 7 Josh Horn FR 32:09.00 8 Ben Bach SR 33:07.00 9 Justin Egan SR 35:47.00 10 Larry Stange SR 39:35.00 11 Kevin Keller FR 43:37.00 12 Chad Schmidt FR 29:46.81 13 Josh Horn FR 31:51.53 14 Ben Bach SR 32:16.20 15 Justin Egan SR 36:28.52 16 Larry Stange SR 38:53.24 17 Kevin Keller FR 41:23.16 18 Casey Weckbach FR 52:46.00 19 Chad Schmidt FR 30:33.01 20 Ben Bach SR 32:56.00 21 Justin Egan SR 36:06.00 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use SMALL with certain restrictions?
I can help you start...
Enter 1,2, 3... in say Col C starting from C2 In D2 type =SMALL(IF($B$2:$B$20="SR",$A$2:$A$20,FALSE),C2) and press CTRL-SHIFT-ENTER This will give you the best time in D2, next best in D3 and so on from the times where year = SR The result I got 1 32:16.2 2 32:56.0 3 33:07.0 4 35:47.0 5 36:06.0 6 36:28.5 7 38:53.2 8 39:35.0 Hope you can build on this... Assumption: Col A and B have the following values; Time Year 33:20.0 JR 37:16.0 SO 48:03.0 JR 29:37.0 FR 32:09.0 FR 33:07.0 SR 35:47.0 SR 39:35.0 SR 43:37.0 FR 29:46.8 FR 31:51.5 FR 32:16.2 SR 36:28.5 SR 38:53.2 SR 41:23.2 FR 52:46.0 FR 30:33.0 FR 32:56.0 SR 36:06.0 SR send the file to me so that I can play around... "thscc1659" wrote: I'm trying to generate a Top 10 times list for each grade level (fr, so, jr, sr) from a spreadsheet containing several years worth of race times. The data are arranged chronologically. Any ideas how I could make a Top 10 Freshman time list? I also want to make a top 10 list for all times, with the caveat that once a runner achieved a top 10 time they could not hold any more of the top 10 (for example the runner with the fastest time also has #s 3,4,6, and 8. Thanks! Below is an excerpt of how the data are arranged. B C D 3 Ben Bach JR 33:20.00 4 Stephano Kaiser SO 37:16.00 5 Larry Stange JR 48:03.00 6 Chad Schmidt FR 29:37.00 7 Josh Horn FR 32:09.00 8 Ben Bach SR 33:07.00 9 Justin Egan SR 35:47.00 10 Larry Stange SR 39:35.00 11 Kevin Keller FR 43:37.00 12 Chad Schmidt FR 29:46.81 13 Josh Horn FR 31:51.53 14 Ben Bach SR 32:16.20 15 Justin Egan SR 36:28.52 16 Larry Stange SR 38:53.24 17 Kevin Keller FR 41:23.16 18 Casey Weckbach FR 52:46.00 19 Chad Schmidt FR 30:33.01 20 Ben Bach SR 32:56.00 21 Justin Egan SR 36:06.00 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use SMALL with certain restrictions?
hi, !
another approach, for the top 10 list for all times (formulae subject to be streamlined): [E3] =min(if($b$3:$b$21=b3,$d$3:$d$21)) <= this is a CSE array-formula [F3] =if(countif(b$3:b3,b3)=1,rank(e3,$e$3:$e$21,1)) [G3] =if(row(a1)$g$2,"",index($b$3:$b$21,match(small($ f$3:$f$21,row(a1)),$f$3:$f$21,0))) [H3] =if(row(a1)$g$2,"",index($e$3:$e$21,match(small($ f$3:$f$21,row(a1)),$f$3:$f$21,0))) copy-down [E3:H3] to cover [E4:H21] range hth, hector. __ OP __ I'm trying to generate a Top 10 times list for each grade level (fr, so, jr, sr) from a spreadsheet containing several years worth of race times. The data are arranged chronologically. Any ideas how I could make a Top 10 Freshman time list? I also want to make a top 10 list for all times, with the caveat that once a runner achieved a top 10 time they could not hold any more of the top 10 (for example the runner with the fastest time also has #s 3,4,6, and 8. Below is an excerpt of how the data are arranged. B C D 3 Ben Bach JR 33:20.00 4 Stephano Kaiser SO 37:16.00 5 Larry Stange JR 48:03.00 6 Chad Schmidt FR 29:37.00 7 Josh Horn FR 32:09.00 8 Ben Bach SR 33:07.00 9 Justin Egan SR 35:47.00 10 Larry Stange SR 39:35.00 11 Kevin Keller FR 43:37.00 12 Chad Schmidt FR 29:46.81 13 Josh Horn FR 31:51.53 14 Ben Bach SR 32:16.20 15 Justin Egan SR 36:28.52 16 Larry Stange SR 38:53.24 17 Kevin Keller FR 41:23.16 18 Casey Weckbach FR 52:46.00 19 Chad Schmidt FR 30:33.01 20 Ben Bach SR 32:56.00 21 Justin Egan SR 36:06.00 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use SMALL with certain restrictions?
hi, again !
i'm sorry... missed to post [G2] =count(f:f) regards, hector. another approach, for the top 10 list for all times (formulae subject to be streamlined): [E3] =min(if($b$3:$b$21=b3,$d$3:$d$21)) <= this is a CSE array-formula [F3] =if(countif(b$3:b3,b3)=1,rank(e3,$e$3:$e$21,1)) [G3] =if(row(a1)$g$2,"",index($b$3:$b$21,match(small($ f$3:$f$21,row(a1)),$f$3:$f$21,0))) [H3] =if(row(a1)$g$2,"",index($e$3:$e$21,match(small($ f$3:$f$21,row(a1)),$f$3:$f$21,0))) copy-down [E3:H3] to cover [E4:H21] range hth, hector. __ OP __ I'm trying to generate a Top 10 times list for each grade level (fr, so, jr, sr) from a spreadsheet containing several years worth of race times. The data are arranged chronologically. Any ideas how I could make a Top 10 Freshman time list? I also want to make a top 10 list for all times, with the caveat that once a runner achieved a top 10 time they could not hold any more of the top 10 (for example the runner with the fastest time also has #s 3,4,6, and 8. Below is an excerpt of how the data are arranged. B C D 3 Ben Bach JR 33:20.00 4 Stephano Kaiser SO 37:16.00 5 Larry Stange JR 48:03.00 6 Chad Schmidt FR 29:37.00 7 Josh Horn FR 32:09.00 8 Ben Bach SR 33:07.00 9 Justin Egan SR 35:47.00 10 Larry Stange SR 39:35.00 11 Kevin Keller FR 43:37.00 12 Chad Schmidt FR 29:46.81 13 Josh Horn FR 31:51.53 14 Ben Bach SR 32:16.20 15 Justin Egan SR 36:28.52 16 Larry Stange SR 38:53.24 17 Kevin Keller FR 41:23.16 18 Casey Weckbach FR 52:46.00 19 Chad Schmidt FR 30:33.01 20 Ben Bach SR 32:56.00 21 Justin Egan SR 36:06.00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif with restrictions | Excel Worksheet Functions | |||
ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
Text Restrictions | Excel Worksheet Functions | |||
data restrictions | Excel Discussion (Misc queries) | |||
How to do look up with restrictions | Excel Discussion (Misc queries) |