Using SMALL Function with select data
 

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

Using SMALL Function with select data
 

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.

--
Biff
Microsoft Excel MVP


"thscc1659" wrote in message
...
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

Using SMALL Function with select data
 

Yes I want names and times.
Thanks

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"thscc1659" wrote in message
...
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

Using SMALL Function with select data
 

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.

Using SMALL Function with select data
 

OK, try this:

Enter this formula in H3:

=MIN(D3:D21)

Enter this formula G3:

=INDEX(B3:B21,MATCH(MIN(D3:D21),D3:D21,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:D$21 H3),D$3:D$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:D$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!


--
Biff
Microsoft Excel MVP


"thscc1659" wrote in message
...
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.

Using SMALL Function with select data
 

That worked perfectly! Thanks for the help.

"T. Valko" wrote:

OK, try this:

Enter this formula in H3:

=MIN(D3:D21)

Enter this formula G3:

=INDEX(B3:B21,MATCH(MIN(D3:D21),D3:D21,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:D$21 H3),D$3:D$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:D$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!


--
Biff
Microsoft Excel MVP


"thscc1659" wrote in message
...
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.

Using SMALL Function with select data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"thscc1659" wrote in message
...
That worked perfectly! Thanks for the help.

"T. Valko" wrote:

OK, try this:

Enter this formula in H3:

=MIN(D3:D21)

Enter this formula G3:

=INDEX(B3:B21,MATCH(MIN(D3:D21),D3:D21,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:D$21 H3),D$3:D$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:D$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!


--
Biff
Microsoft Excel MVP


"thscc1659" wrote in message
...
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.