Dividing a school-class into (almost) equally sized groups
 

What I want to do might be extremely complicated or it might be super-easy
and I'm just not able to wrap my head around it...

I have a large number of students which will vary from situation to
situation (right now it's 29) and I want to be able to divide that class up
into groups - anything from 5-10 groups, usually. What I want to be able to
do is generate the sizes of those groups as evenly as possible - if I want to
make 8 groups from a class of 29, I want those groups to be
(4,4,4,4,4,3,3,3), and if I want to make 7 groups I want the output to be
(5,4,4,4,4,4,4) so that no group becomes too small. I will input the number
of groups and the class size each time, and then I want excel to generate the
sizes of the groups for me to use in other functions.

Is there a good way you can think of that would solve this, either through a
VBA function or some clever cell formulas? Thanks,

Babymech

Dividing a school-class into (almost) equally sized groups
 

In A1 put the number of students
In A2 put the number of groups

In B1 enter:
= ROUND(ROW()/$A$2*$A$1,0)

In B2 enter:
= IF(ROW()<=$A$2,ROUND(ROW()/$A$2*$A$1,0)-SUM(B$1:B1),"")
and copy down
--
Gary''s Student - gsnu200906


"Babymech" wrote:

What I want to do might be extremely complicated or it might be super-easy
and I'm just not able to wrap my head around it...

I have a large number of students which will vary from situation to
situation (right now it's 29) and I want to be able to divide that class up
into groups - anything from 5-10 groups, usually. What I want to be able to
do is generate the sizes of those groups as evenly as possible - if I want to
make 8 groups from a class of 29, I want those groups to be
(4,4,4,4,4,3,3,3), and if I want to make 7 groups I want the output to be
(5,4,4,4,4,4,4) so that no group becomes too small. I will input the number
of groups and the class size each time, and then I want excel to generate the
sizes of the groups for me to use in other functions.

Is there a good way you can think of that would solve this, either through a
VBA function or some clever cell formulas? Thanks,

Babymech

Dividing a school-class into (almost) equally sized groups
 

That worked our perfectly - thank you. I was working with something similar
based on ROW(), but I never got my head around the numbers, and I didn't
think of your simple but excellent solution for subtracting the range of all
numbers above. Thanks!

(Right I get an error message when I try to vote "Yes" on your answer, but
as soon as it lets me I will)

"Gary''s Student" wrote:

In A1 put the number of students
In A2 put the number of groups

In B1 enter:
= ROUND(ROW()/$A$2*$A$1,0)

In B2 enter:
= IF(ROW()<=$A$2,ROUND(ROW()/$A$2*$A$1,0)-SUM(B$1:B1),"")
and copy down
--
Gary''s Student - gsnu200906


"Babymech" wrote:

What I want to do might be extremely complicated or it might be super-easy
and I'm just not able to wrap my head around it...

I have a large number of students which will vary from situation to
situation (right now it's 29) and I want to be able to divide that class up
into groups - anything from 5-10 groups, usually. What I want to be able to
do is generate the sizes of those groups as evenly as possible - if I want to
make 8 groups from a class of 29, I want those groups to be
(4,4,4,4,4,3,3,3), and if I want to make 7 groups I want the output to be
(5,4,4,4,4,4,4) so that no group becomes too small. I will input the number
of groups and the class size each time, and then I want excel to generate the
sizes of the groups for me to use in other functions.

Is there a good way you can think of that would solve this, either through a
VBA function or some clever cell formulas? Thanks,

Babymech

Dividing a school-class into (almost) equally sized groups
 

You are very welcome!
--
Gary''s Student - gsnu200906


"Babymech" wrote:

That worked our perfectly - thank you. I was working with something similar
based on ROW(), but I never got my head around the numbers, and I didn't
think of your simple but excellent solution for subtracting the range of all
numbers above. Thanks!

(Right I get an error message when I try to vote "Yes" on your answer, but
as soon as it lets me I will)

"Gary''s Student" wrote:

In A1 put the number of students
In A2 put the number of groups

In B1 enter:
= ROUND(ROW()/$A$2*$A$1,0)

In B2 enter:
= IF(ROW()<=$A$2,ROUND(ROW()/$A$2*$A$1,0)-SUM(B$1:B1),"")
and copy down
--
Gary''s Student - gsnu200906


"Babymech" wrote:

What I want to do might be extremely complicated or it might be super-easy
and I'm just not able to wrap my head around it...

I have a large number of students which will vary from situation to
situation (right now it's 29) and I want to be able to divide that class up
into groups - anything from 5-10 groups, usually. What I want to be able to
do is generate the sizes of those groups as evenly as possible - if I want to
make 8 groups from a class of 29, I want those groups to be
(4,4,4,4,4,3,3,3), and if I want to make 7 groups I want the output to be
(5,4,4,4,4,4,4) so that no group becomes too small. I will input the number
of groups and the class size each time, and then I want excel to generate the
sizes of the groups for me to use in other functions.

Is there a good way you can think of that would solve this, either through a
VBA function or some clever cell formulas? Thanks,

Babymech