How to use SMALL with certain restrictions?

[thscc1659]

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

 

[Sheeloo]

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

 

[Héctor Miguel]

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 __

 

[Héctor Miguel]

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