Using SMALL Function with select data

[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

 

[T. Valko]

Do you want both the names and times? It will make it easier to get the
overall top 10 and exclude multiple best times from the same name if you
also want to pull the associated names.

 

[thscc1659]

Yes I want names and times.
Thanks

Do you want both the names and times? It will make it easier to get the
overall top 10 and exclude multiple best times from the same name if you
also want to pull the associated names.

 

[thscc1659]

I've figured out how to do Top 10 by class, I just need Top 10 list excluding
multiple best times. Thanks for the help.

 

[T. Valko]

OK, try this:

Enter this formula in H3:

=MIN(D321)

Enter this formula G3:

=INDEX(B3:B21,MATCH(MIN(D321),D321,0))

Those will return the name and the lowest time.

Now, enter this array formula** in H4:

=MIN(IF((ISNA(MATCH(B$3:B$21,G$3:G3,0)))*(D$3$21>H3),D$3$21))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula in G4

=INDEX(B$3:B$21,MATCH(H4,D$3$21,0))

Select both G4 and H4 and copy down as needed.

Format the time cells as desired.

If there are ties (duplicate times) then this will get *really* complicated!

 

[thscc1659]

That worked perfectly! Thanks for the help.

OK, try this:

Enter this formula in H3:

=MIN(D321)

Enter this formula G3:

=INDEX(B3:B21,MATCH(MIN(D321),D321,0))

Those will return the name and the lowest time.

Now, enter this array formula** in H4:

=MIN(IF((ISNA(MATCH(B$3:B$21,G$3:G3,0)))*(D$3$21>H3),D$3$21))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula in G4

=INDEX(B$3:B$21,MATCH(H4,D$3$21,0))

Select both G4 and H4 and copy down as needed.

Format the time cells as desired.

If there are ties (duplicate times) then this will get *really* complicated!

 

[T. Valko]

You're welcome. Thanks for the feedback!