Divide Ranks into two teams (mathematical guru challenge)
 

Hi!

Got a data manipulation challenge he

We've got a group of people, who through a completely unrelated system have
been given numerical ranks or scores. I'm trying to find a way to divide
these scores (people) into two teams with totals scores (all the scores added
up) as even as possible.
For example:
Person One has a score of 5
Person Two: 4
Person Three: 2
Person Four: 5

I'm looking for some kind of function to take these 4 people (the real stuff
has a lot more people) and divide them into two teams that have total scores
as close to even as possible.

Any ideas?

I'm using XL2003 but I'm fairly confident that you can do
it in XL2000. For step-by-step instructions, see:

http://www.contextures.com/xlcomments02.html#Picture

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

Got a data manipulation challenge he

We've got a group of people, who through a completely
unrelated system have
been given numerical ranks or scores. I'm trying to
find a way to divide
these scores (people) into two teams with totals scores
(all the scores added
up) as even as possible.
For example:
Person One has a score of 5
Person Two: 4
Person Three: 2
Person Four: 5

I'm looking for some kind of function to take these 4
people (the real stuff
has a lot more people) and divide them into two teams
that have total scores
as close to even as possible.

Any ideas?
.

Sorry, responded to the wrong post.

-----Original Message-----
I'm using XL2003 but I'm fairly confident that you can
do
it in XL2000. For step-by-step instructions, see:

http://www.contextures.com/xlcomments02.html#Picture

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

Got a data manipulation challenge he

We've got a group of people, who through a completely
unrelated system have
been given numerical ranks or scores. I'm trying to
find a way to divide
these scores (people) into two teams with totals scores
(all the scores added
up) as even as possible.
For example:
Person One has a score of 5
Person Two: 4
Person Three: 2
Person Four: 5

I'm looking for some kind of function to take these 4
people (the real stuff
has a lot more people) and divide them into two teams
that have total scores
as close to even as possible.

Any ideas?
.

.

This time I'm responding to the right post! Here's one
method that seems promising after several trial runs
although it didn't always come out with the optimal
solution.

Let's assume your people are in A1:A20 and their rank is
in B1:B20.

In C1 put this and fill down to C20:

=SUM($B$1:B1)

In D1 insert a 1, and in D2 put this and fill down to D20:

=IF(C2<SUM(B:B)/2,1,IF(B2=SUM(B:B)/2-OFFSET($C$1,SUM
($D$1:D1)-1,),1,""))

All the members of team #1 have a 1 in column D. Team #2
does not have a 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

Got a data manipulation challenge he

We've got a group of people, who through a completely
unrelated system have
been given numerical ranks or scores. I'm trying to
find a way to divide
these scores (people) into two teams with totals scores
(all the scores added
up) as even as possible.
For example:
Person One has a score of 5
Person Two: 4
Person Three: 2
Person Four: 5

I'm looking for some kind of function to take these 4
people (the real stuff
has a lot more people) and divide them into two teams
that have total scores
as close to even as possible.

Any ideas?
.

Thanks for your help Jason!

Apparantly however I'm doing something wrong since it's coming up with crazy
results. When you say "fill down to.." what do you mean? Copy the code from
here? Or copy the cell so it updates row and column info?

TIA,
Jim


"Jason Morin" wrote:

This time I'm responding to the right post! Here's one
method that seems promising after several trial runs
although it didn't always come out with the optimal
solution.

Let's assume your people are in A1:A20 and their rank is
in B1:B20.

In C1 put this and fill down to C20:

=SUM($B$1:B1)

In D1 insert a 1, and in D2 put this and fill down to D20:

=IF(C2<SUM(B:B)/2,1,IF(B2=SUM(B:B)/2-OFFSET($C$1,SUM
($D$1:D1)-1,),1,""))

All the members of team #1 have a 1 in column D. Team #2
does not have a 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

Got a data manipulation challenge he

We've got a group of people, who through a completely
unrelated system have
been given numerical ranks or scores. I'm trying to
find a way to divide
these scores (people) into two teams with totals scores
(all the scores added
up) as even as possible.
For example:
Person One has a score of 5
Person Two: 4
Person Three: 2
Person Four: 5

I'm looking for some kind of function to take these 4
people (the real stuff
has a lot more people) and divide them into two teams
that have total scores
as close to even as possible.

Any ideas?
.