I was speaking with a teacher colleague who would like to create an
excel sheet which would assign students randomly to six groups, with
each group having four students.

I thought that I could use the Solver function in conjunction with the
RANDOMBETWEEN function to solve this, but to no avail. Any ideas would
be greatly appreciated. My approach is outlined below.

Sincerely,
Tim Maguire


Approach:
1) Have a list of 24 students, with a column for the group they are
assigned to (number 1-6).
2) The group colmun is generated using the RANDOMBETWEEN function.
3) I use SUMIF statements to calcuate the occurence of each group
number calculated using the RANDOMBETWEEN function.
4) I then run solver with the objective of setting each of the SUMIF
statements equal to 4. That way I have 6 groups of 4 students.

I think, due to the nonlinearity of sub-routines (such as SUMIF) this
problem is not solvable. Thus any help is greatly appreciated.


--
timjm
------------------------------------------------------------------------
timjm's Profile: http://www.excelforum.com/member.php...o&userid=37463
View this thread: http://www.excelforum.com/showthread...hreadid=571231

If I reformat it as a binary linear optimization model it appears to
work. Any other ideas?


--
timjm
------------------------------------------------------------------------
timjm's Profile: http://www.excelforum.com/member.php...o&userid=37463
View this thread: http://www.excelforum.com/showthread...hreadid=571231

The normal card shuffle routine is to go through the list one cell at a time
and swap it with another cell selected at random from the list. You then
take the groups from the list four at a time.


"timjm" wrote in
message ...

I was speaking with a teacher colleague who would like to create an
excel sheet which would assign students randomly to six groups, with
each group having four students.

I thought that I could use the Solver function in conjunction with the
RANDOMBETWEEN function to solve this, but to no avail. Any ideas would
be greatly appreciated. My approach is outlined below.

Sincerely,
Tim Maguire


Approach:
1) Have a list of 24 students, with a column for the group they are
assigned to (number 1-6).
2) The group colmun is generated using the RANDOMBETWEEN function.
3) I use SUMIF statements to calcuate the occurence of each group
number calculated using the RANDOMBETWEEN function.
4) I then run solver with the objective of setting each of the SUMIF
statements equal to 4. That way I have 6 groups of 4 students.

I think, due to the nonlinearity of sub-routines (such as SUMIF) this
problem is not solvable. Thus any help is greatly appreciated.


--
timjm
------------------------------------------------------------------------
timjm's Profile:
http://www.excelforum.com/member.php...o&userid=37463
View this thread: http://www.excelforum.com/showthread...hreadid=571231