Combination Formula

[Lee Grant]

Hi there,

I have a list of data (runners' race times actually) and I want to produce a
sheet shows certain combinations of data.

The data sheet has the runner data in rows with headers for the columns
(name, category, gender, finish time, etc.).

I've been using the =small function to find the quickest times (which is
fine) but how can I find, for example, the quickest male time?

Ideally I'd think I need some sort of combination of =SMALL with a AND (ie,
the lowest time with someone with a category of 'M' - but this doesn't seem
to be getting me very far.

Also I'd then like to go on and list things like - three quickest ladies,
all in the same team. This obviously would be some combination of the
finish time and the team column.

Can someone stop my head from hurting?

Any help is really appreciated,

Kindest Regards,

Lee

 

[Bob Phillips]

=MIN(IF(gender="M",finish_time))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

 

[Bernie Deitrick]

Lee,

Array enter (enter using Ctrl-Shift-Enter instead of just Enter)

=SMALL(IF(A2:A200="M",B2:B200),1)

But easier is:

=SMALL(IF($A$2:$A$200="Male",$B$2:$B$200),ROW(A1))

and then copied down for as many places as you need.

And for age groups (Again, array entered):

=SMALL(IF($A$2:$A$200="Male",IF($B$2:$B$200="Old guys",$C$2:$C$9)),ROW(A1))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Lee,

I should have added that I have a timing sheet for races - you enter all the runners' data prior to
the start of the race, then click at the start of the race, and fill in numbers as runners cross the
finish line to record times, and places are automatically assigned. It doesn't do teams, but could
be easily modified to include that as well. If you are interested in trying it, contact me
privately (Take out the spaces, etc) and I will send you the workbook.

HTH,
Bernie
MS Excel MVP

 

[Lee Grant]

Hi Bernie,

Many thanks for the reply.

I've been using your last suggestion with a lot of success:

=SMALL(IF($A$2:$A$200="Male",IF($B$2:$B$200="Old guys",$C$2:$C$9)),ROW(A1))

I wondered if there is a way to replace 'Old guys' with a variable? Is
there a way to to scan the results list and to find the top team (ie the
three quickest runners that all belong to the same team), then the second
team (the second group of three quickest runners - obviously some of those
runners may be faster than the some in the first winning team) and then
finally the third team.

I need to do this for Male and Femle.

The teams for the race are not decided prior to the race.

I've tried to work it out but my brain just doesn't work that way.

Thanks anyway for the previous - excellent!!
being out the top three

 

[Bernie Deitrick]

Lee,

You would need an additional column of formulas to pick the top three runners:

=C2<=SMALL(IF($A$2:$A$200=A2,IF($B$2:$B$200=B2,$C$2:$C$200)),3)

copy down to match. Then you could use a pivot table, selecting TRUE for that new column, and
sorting based on the sum of the times Ascending.

HTH,
Bernie
MS Excel MVP

 

[Lee Grant]

Hi Bernie,

Thanks for that formula. I've tried to use it but I'm unsure of the formula
mapping.

I can see that we're using three columns A, B & C - what information does
you formula expect in each column (eg A column = Name, B column = Team, C
column = time).

The reason I ask is that the data is actually in another worksheet from the
one the formula will be in. I just couldn't get me head around it to be
able to try.

Sorry & many, many thanks!

 

[Bernie Deitrick]

Lee,

You had posted this:

=SMALL(IF($A$2:$A$200="Male",IF($B$2:$B$200="Old guys",$C$2:$C$9)),ROW(A1))

so I was basing my formula on A being gender, B being team name and C being time:

So, in a cell on row 2, array enter this formula (enter using Ctrl-Shift Enter)

=C2<=SMALL(IF($A$2:$A$200=A2,IF($B$2:$B$200=B2,$C$2:$C$200)),3)

Or maybe this, in case there is no team name (again, using Ctrl-Shift-Enter)
=IF(B2<>"", C2<=SMALL(IF($A$2:$A$200=A2,IF($B$2:$B$200=B2,$C$2:$C$200)),3))

That will give the TRUE and FALSE to feed into a pivot table of team results... I can send you an
example if you are not able to get it to work.

HTH,
Bernie
MS Excel MVP