Divide Ranks into two teams (mathematical guru challenge)

[Guest]

Hi!

Got a data manipulation challenge here:

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?

 

[Jason Morin]

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

 

[Jason Morin]

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

.

 

[Jason Morin]

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$11)-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

 

[Guest]

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