Trying to do too much?

[Mike L.]

I have a column (well, multiple columns, but I can copy the formula
from column to column) that is an age group/class (baseball
tournaments). Then, then 4 rows of top teams in the tournament, then
the amount of teams in the tournament. The next cell starts a new
tournament (with 4 top teams, then the amount of teams)

So it's like;
New York
LA
Chicago
Memphis
14
LA
Montreal
Chicago
Austin
22

What I need to give each city a value based on their place and the
amount of teams in the tournament.

It is;
<7; 1st=2, 2nd=1, 3rd=.5
8-13: 1st=4, 2nd=2, 3rd=1, 4th=.5
>14; 1st=6, 2nd=4, 3rd=2, 4th=1

Is there a way to do this? Would I have to make a column that assigns
values of cells depending on the amount of teams? I'm kind of lost, if
anyone can gimme an idea of where to go, I'd really appreciate it.
Thanks!

[Ardus Petus]

One way:
=IF(MOD(ROW(),5)=0,"",VLOOKUP(OFFSET(A1,5-MOD(ROW(),5),0),Points!$A$2:$E$4,MOD(ROW(),5)+1,1))

Beware: it's all based on row number: firts team name MUST be on row 1

It uses a lookup table in sheet Points

See example: http://cjoint.com/?ghsAPWpzcH

HTH
--
AP

"Mike L." <[email protected]> a écrit dans le message de news:
[email protected]...
>I have a column (well, multiple columns, but I can copy the formula
> from column to column) that is an age group/class (baseball
> tournaments). Then, then 4 rows of top teams in the tournament, then
> the amount of teams in the tournament. The next cell starts a new
> tournament (with 4 top teams, then the amount of teams)
>
> So it's like;
> New York
> LA
> Chicago
> Memphis
> 14
> LA
> Montreal
> Chicago
> Austin
> 22
>
> What I need to give each city a value based on their place and the
> amount of teams in the tournament.
>
> It is;
> <7; 1st=2, 2nd=1, 3rd=.5
> 8-13: 1st=4, 2nd=2, 3rd=1, 4th=.5
>>14; 1st=6, 2nd=4, 3rd=2, 4th=1
>
> Is there a way to do this? Would I have to make a column that assigns
> values of cells depending on the amount of teams? I'm kind of lost, if
> anyone can gimme an idea of where to go, I'd really appreciate it.
> Thanks!
>

[Mike L.]

Thanks, that's almost exactly what I want to do, how would I then have
a group of cells at the bottom (or wherever), that have a running total
how many points each team has gotten? We may have up to 25 different
teams in the top four, then 15 age divisions. Is there a way to
automatically generate a list of teams that have placed, then display
how many points each of these teams has accumulated (this would be the
number generated by the aforementioned formula),

But, wow, that works quite well. Thanks a lot!


Ardus Petus wrote:
> One way:
> =3DIF(MOD(ROW(),5)=3D0,"",VLOOKUP(OFFSET(A1,5-MOD(ROW(),5),0),Points!$A$2=
:$E$4,MOD(ROW(),5)+1,1))
>
> Beware: it's all based on row number: firts team name MUST be on row 1
>
> It uses a lookup table in sheet Points
>
> See example: http://cjoint.com/?ghsAPWpzcH
>
> HTH
> --
> AP
>
> "Mike L." <[email protected]> a =E9crit dans le message de news:
> [email protected]...
> >I have a column (well, multiple columns, but I can copy the formula
> > from column to column) that is an age group/class (baseball
> > tournaments). Then, then 4 rows of top teams in the tournament, then
> > the amount of teams in the tournament. The next cell starts a new
> > tournament (with 4 top teams, then the amount of teams)
> >
> > So it's like;
> > New York
> > LA
> > Chicago
> > Memphis
> > 14
> > LA
> > Montreal
> > Chicago
> > Austin
> > 22
> >
> > What I need to give each city a value based on their place and the
> > amount of teams in the tournament.
> >
> > It is;
> > <7; 1st=3D2, 2nd=3D1, 3rd=3D.5
> > 8-13: 1st=3D4, 2nd=3D2, 3rd=3D1, 4th=3D.5
> >>14; 1st=3D6, 2nd=3D4, 3rd=3D2, 4th=3D1
> >
> > Is there a way to do this? Would I have to make a column that assigns
> > values of cells depending on the amount of teams? I'm kind of lost, if
> > anyone can gimme an idea of where to go, I'd really appreciate it.
> > Thanks!
> >

[Ardus Petus]

Please post some sample data, so I can figure out what you have, and what
you want.

You can upload your workbook on http://cjoint.com/ and post back the link.
(the site is in french, but it's pretty esy to use)

Cheers,
--
AP

"Mike L." <[email protected]> a écrit dans le message de news:
[email protected]...
Thanks, that's almost exactly what I want to do, how would I then have
a group of cells at the bottom (or wherever), that have a running total
how many points each team has gotten? We may have up to 25 different
teams in the top four, then 15 age divisions. Is there a way to
automatically generate a list of teams that have placed, then display
how many points each of these teams has accumulated (this would be the
number generated by the aforementioned formula),

But, wow, that works quite well. Thanks a lot!


Ardus Petus wrote:
> One way:
> =IF(MOD(ROW(),5)=0,"",VLOOKUP(OFFSET(A1,5-MOD(ROW(),5),0),Points!$A$2:$E$4,MOD(ROW(),5)+1,1))
>
> Beware: it's all based on row number: firts team name MUST be on row 1
>
> It uses a lookup table in sheet Points
>
> See example: http://cjoint.com/?ghsAPWpzcH
>
> HTH
> --
> AP
>
> "Mike L." <[email protected]> a écrit dans le message de news:
> [email protected]...
> >I have a column (well, multiple columns, but I can copy the formula
> > from column to column) that is an age group/class (baseball
> > tournaments). Then, then 4 rows of top teams in the tournament, then
> > the amount of teams in the tournament. The next cell starts a new
> > tournament (with 4 top teams, then the amount of teams)
> >
> > So it's like;
> > New York
> > LA
> > Chicago
> > Memphis
> > 14
> > LA
> > Montreal
> > Chicago
> > Austin
> > 22
> >
> > What I need to give each city a value based on their place and the
> > amount of teams in the tournament.
> >
> > It is;
> > <7; 1st=2, 2nd=1, 3rd=.5
> > 8-13: 1st=4, 2nd=2, 3rd=1, 4th=.5
> >>14; 1st=6, 2nd=4, 3rd=2, 4th=1
> >
> > Is there a way to do this? Would I have to make a column that assigns
> > values of cells depending on the amount of teams? I'm kind of lost, if
> > anyone can gimme an idea of where to go, I'd really appreciate it.
> > Thanks!
> >

[Mike L.]

http://cjoint.com/data/giuMn4ocIU.htm

Like I said, what I need is a list of teams (at the bottom, or
whatever) for each Age/Division (Each column can be completely seperate
for all intents and purposes) with the given point structure. Although
the amount of teams in each tournament will be the 5th cell in the
column, which will decide how many points each team gets, I don't have
the data yet.




Ardus Petus wrote:
> Please post some sample data, so I can figure out what you have, and what
> you want.
>
> You can upload your workbook on http://cjoint.com/ and post back the link.
> (the site is in french, but it's pretty esy to use)
>
> Cheers,
> --
> AP
>
> "Mike L." <[email protected]> a =E9crit dans le message de news:
> [email protected]...
> Thanks, that's almost exactly what I want to do, how would I then have
> a group of cells at the bottom (or wherever), that have a running total
> how many points each team has gotten? We may have up to 25 different
> teams in the top four, then 15 age divisions. Is there a way to
> automatically generate a list of teams that have placed, then display
> how many points each of these teams has accumulated (this would be the
> number generated by the aforementioned formula),
>
> But, wow, that works quite well. Thanks a lot!
>
>
> Ardus Petus wrote:
> > One way:
> > =3DIF(MOD(ROW(),5)=3D0,"",VLOOKUP(OFFSET(A1,5-MOD(ROW(),5),0),Points!$A=
$2:$E$4,MOD(ROW(),5)+1,1))
> >
> > Beware: it's all based on row number: firts team name MUST be on row 1
> >
> > It uses a lookup table in sheet Points
> >
> > See example: http://cjoint.com/?ghsAPWpzcH
> >
> > HTH
> > --
> > AP
> >
> > "Mike L." <[email protected]> a =E9crit dans le message de news:
> > [email protected]...
> > >I have a column (well, multiple columns, but I can copy the formula
> > > from column to column) that is an age group/class (baseball
> > > tournaments). Then, then 4 rows of top teams in the tournament, then
> > > the amount of teams in the tournament. The next cell starts a new
> > > tournament (with 4 top teams, then the amount of teams)
> > >
> > > So it's like;
> > > New York
> > > LA
> > > Chicago
> > > Memphis
> > > 14
> > > LA
> > > Montreal
> > > Chicago
> > > Austin
> > > 22
> > >
> > > What I need to give each city a value based on their place and the
> > > amount of teams in the tournament.
> > >
> > > It is;
> > > <7; 1st=3D2, 2nd=3D1, 3rd=3D.5
> > > 8-13: 1st=3D4, 2nd=3D2, 3rd=3D1, 4th=3D.5
> > >>14; 1st=3D6, 2nd=3D4, 3rd=3D2, 4th=3D1
> > >
> > > Is there a way to do this? Would I have to make a column that assigns
> > > values of cells depending on the amount of teams? I'm kind of lost, if
> > > anyone can gimme an idea of where to go, I'd really appreciate it.
> > > Thanks!
> > >

[Mike L.]

My mistake! I uploaded the wrong file.

This is correct;

http://cjoint.com/?giuTuLFOnU



Mike L. wrote:
> http://cjoint.com/data/giuMn4ocIU.htm
>
> Like I said, what I need is a list of teams (at the bottom, or
> whatever) for each Age/Division (Each column can be completely seperate
> for all intents and purposes) with the given point structure. Although
> the amount of teams in each tournament will be the 5th cell in the
> column, which will decide how many points each team gets, I don't have
> the data yet.
>
>
>
>
> Ardus Petus wrote:
> > Please post some sample data, so I can figure out what you have, and wh=
at
> > you want.
> >
> > You can upload your workbook on http://cjoint.com/ and post back the li=
nk.
> > (the site is in french, but it's pretty esy to use)
> >
> > Cheers,
> > --
> > AP
> >
> > "Mike L." <[email protected]> a =E9crit dans le message de news:
> > [email protected]...
> > Thanks, that's almost exactly what I want to do, how would I then have
> > a group of cells at the bottom (or wherever), that have a running total
> > how many points each team has gotten? We may have up to 25 different
> > teams in the top four, then 15 age divisions. Is there a way to
> > automatically generate a list of teams that have placed, then display
> > how many points each of these teams has accumulated (this would be the
> > number generated by the aforementioned formula),
> >
> > But, wow, that works quite well. Thanks a lot!
> >
> >
> > Ardus Petus wrote:
> > > One way:
> > > =3DIF(MOD(ROW(),5)=3D0,"",VLOOKUP(OFFSET(A1,5-MOD(ROW(),5),0),Points!=
$A$2:$E$4,MOD(ROW(),5)+1,1))
> > >
> > > Beware: it's all based on row number: firts team name MUST be on row 1
> > >
> > > It uses a lookup table in sheet Points
> > >
> > > See example: http://cjoint.com/?ghsAPWpzcH
> > >
> > > HTH
> > > --
> > > AP
> > >
> > > "Mike L." <[email protected]> a =E9crit dans le message de news:
> > > [email protected]...
> > > >I have a column (well, multiple columns, but I can copy the formula
> > > > from column to column) that is an age group/class (baseball
> > > > tournaments). Then, then 4 rows of top teams in the tournament, then
> > > > the amount of teams in the tournament. The next cell starts a new
> > > > tournament (with 4 top teams, then the amount of teams)
> > > >
> > > > So it's like;
> > > > New York
> > > > LA
> > > > Chicago
> > > > Memphis
> > > > 14
> > > > LA
> > > > Montreal
> > > > Chicago
> > > > Austin
> > > > 22
> > > >
> > > > What I need to give each city a value based on their place and the
> > > > amount of teams in the tournament.
> > > >
> > > > It is;
> > > > <7; 1st=3D2, 2nd=3D1, 3rd=3D.5
> > > > 8-13: 1st=3D4, 2nd=3D2, 3rd=3D1, 4th=3D.5
> > > >>14; 1st=3D6, 2nd=3D4, 3rd=3D2, 4th=3D1
> > > >
> > > > Is there a way to do this? Would I have to make a column that assig=
ns
> > > > values of cells depending on the amount of teams? I'm kind of lost,=
if
> > > > anyone can gimme an idea of where to go, I'd really appreciate it.
> > > > Thanks!
> > > >