|  | Combination Formula | | |
| | | Lee Grant |  |
|  Posted: Fri Aug 08, 2008 1:36 pm Post subject: Combination Formula |  |
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 | |
| | Posted: Fri Aug 08, 2008 3:18 pm Post subject: Re: Combination Formula | |
| |  | |
=MIN(IF(gender="M",finish_time))
which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter
--
__________________________________
HTH
Bob
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
| Quote: | 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 |
|
| |
| | | Bernie Deitrick | |
| | Posted: Fri Aug 08, 2008 3:26 pm Post subject: Re: Combination Formula | |
| | | |
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
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
| Quote: | 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 |
|
| |
| | | Bernie Deitrick | |
| | Posted: Fri Aug 08, 2008 3:35 pm Post subject: Re: Combination Formula | |
| | | |
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" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
| Quote: | 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 |
|
| |
| | | Lee Grant | |
| | Posted: Tue Aug 12, 2008 6:46 pm Post subject: Re: Combination Formula | |
| | | |
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" <deitbe @ consumer dot org> wrote in message
news:eUXflvX%23IHA.524@TK2MSFTNGP06.phx.gbl...
| Quote: | 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
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
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
|
|
| |
| | | Bernie Deitrick | |
| | Posted: Mon Aug 18, 2008 12:37 pm Post subject: Re: Combination Formula | |
| | | |
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" <lee@inspirationcomputers.com> wrote in message
news:56D55FB4-E111-42E4-B83F-1271D5BAD640@microsoft.com...
| Quote: | 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" <deitbe @ consumer dot org> wrote in message
news:eUXflvX%23IHA.524@TK2MSFTNGP06.phx.gbl...
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
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
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
|
|
| |
| | | Lee Grant | |
| | Posted: Wed Aug 27, 2008 1:33 pm Post subject: Re: Combination Formula | |
| | | |
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" <deitbe @ consumer dot org> wrote in message
news:%23WOk6$TAJHA.4852@TK2MSFTNGP04.phx.gbl...
| Quote: | 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" <lee@inspirationcomputers.com> wrote in message
news:56D55FB4-E111-42E4-B83F-1271D5BAD640@microsoft.com...
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" <deitbe @ consumer dot org> wrote in message
news:eUXflvX%23IHA.524@TK2MSFTNGP06.phx.gbl...
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
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
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
|
|
| |
| | | Bernie Deitrick | |
| | Posted: Wed Aug 27, 2008 1:56 pm Post subject: Re: Combination Formula | |
| | | |
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
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:8D12F47D-173E-4467-A1B7-FAD458933C4E@microsoft.com...
| Quote: | 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" <deitbe @ consumer dot org> wrote in message
news:%23WOk6$TAJHA.4852@TK2MSFTNGP04.phx.gbl...
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" <lee@inspirationcomputers.com> wrote in message
news:56D55FB4-E111-42E4-B83F-1271D5BAD640@microsoft.com...
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" <deitbe @ consumer dot org> wrote in message
news:eUXflvX%23IHA.524@TK2MSFTNGP06.phx.gbl...
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
"Lee Grant" <lee@inspirationcomputers.com> wrote in message
news:17251BB0-CF5E-439E-A19D-E328EF90B3F5@microsoft.com...
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
|
|
| |
|
|
