math wizard needed.

[kevindmorgan]

Who's up for a challenge? I am sure someone is smart enough to figure this out. I have a way of doing it, but I am sure there is an easier way.

Bottom line up-front. I need to separate students so that there are roughly equal numbers of students over 1-5 periods. The reason I say "roughly" is because there is a catch. The number of students per-period needs to be an even number of students when possible. For example:

16 students, 4 periods...the desired result would be 4 students in each of the 4 periods. That's simple, but...

If there were 52 students, the result could not be 13 students in each of the 4 periods, since they should be even numbers when possible. It would be 14,12,14,12 or similar. (12,14,14,12, etc.)

At the time this process takes place, the TOTAL number of students per class is already in one column...say Column A. The number of periods (from 1-5) is also known at this point. Let's say this number is in column B.

In columns C,D,E,F,G (representing periods) would be filled with the student numbers (or a zero if none are in the period).

The student numbers can be anywhere from 2-60, but I'd think a solution would work with any number of students.

If anyone finds a solution for this, you are indeed worthy of my praise!

[Gary''s Student]

Pleased be advised that this comes from the math challenged:

1. divide the number of students by the number of classes and round down to
the next integer

2. if the result is an odd number, then reduce it by one.

3. assign this number to each class except the last class

4. assign to the last class the number of students needed to complete the
allocation


This method will result in all the classes having equal numbers (except
possibly the last) and all the classes except possibly the last having an
equal number of students.
--
Gary''s Student


"kevindmorgan" wrote:

>
> Who's up for a challenge? I am sure someone is smart enough to figure
> this out. I have a way of doing it, but I am sure there is an easier
> way.
>
> Bottom line up-front. I need to separate students so that there are
> roughly equal numbers of students over 1-5 periods. The reason I say
> "roughly" is because there is a catch. The number of students
> per-period needs to be an even number of students when possible. For
> example:
>
> 16 students, 4 periods...the desired result would be 4 students in each
> of the 4 periods. That's simple, but...
>
> If there were 52 students, the result could not be 13 students in each
> of the 4 periods, since they should be even numbers when possible. It
> would be 14,12,14,12 or similar. (12,14,14,12, etc.)
>
> At the time this process takes place, the TOTAL number of students per
> class is already in one column...say Column A. The number of periods
> (from 1-5) is also known at this point. Let's say this number is in
> column B.
>
> In columns C,D,E,F,G (representing periods) would be filled with the
> student numbers (or a zero if none are in the period).
>
> The student numbers can be anywhere from 2-60, but I'd think a solution
> would work with any number of students.
>
> If anyone finds a solution for this, you are indeed worthy of my
> praise!
>
>
> --
> kevindmorgan
> ------------------------------------------------------------------------
> kevindmorgan's Profile: http://www.excelforum.com/member.php...o&userid=32232
> View this thread: http://www.excelforum.com/showthread...hreadid=533452
>
>

[Ronald Dodge]

So you want to have a numbre of students that's divisible by 2. What if you
have an odd number of students over the course of the year, say 45 students?
What do you do then?

Here's one possible solution:

To start off, we will need to divide the total number of students over the
course of the year by both the number of periods and the divisor that you
are wanting to use, which in this case is 2.

Sub StudentAlloc(StudDiv as Long)
Dim I as Long, MinPerPeriod as Long, StudMod as Long, Periods as Long, Row
as Long, TotalDiv as Long
Dim StudTotal as Long, StudRem as Long, wsh as Worksheet
Set wsh = Thisworkbook.Worksheets("StudentAllocation") 'This assumes the
information is stored on the worksheet named "StudentAllocation".
FirstRow = 5 'Assuming the data actually starts on Row 5.
If wsh.Range("A65536").End(xlUp).Row > 4 Then
LastRow = wsh.Range("A65536").End(xlUp).Row
Else
MsgBox "There's no data available to process this code.",48
Exit Sub
End If
wsh.Range("C" & CStr(FirstRow) & ":G" & CStr(LastRow)).ClearContents
For Row = FirstRow to LastRow Step 1
StudTotal = wsh.Range("A" & CStr(Row)).Value
Periods = wsh.Range("B" & CStr(Row)).Value
TotalDiv = Periods * StudDiv
MinPerPeriod = StudTotal\TotalDiv 'Note, this is an integer div
operator, not the normal div operator.
StudMod = StudTotal Mod TotalDiv
StudRem = 0
For I = 3 to Periods+2 Step 1
If StudRem + StudMod >= TotalDiv Then
StudRem = StudRem + StudMod - TotalDiv
wsh.Cells(Row,I).Value = MinPerPeriod * StudDiv + StudDiv
Else
StudRem = StudRem + StudMod
wsh.Cells(Row,I).Value = MinPerPeriod * StudDiv
End If
Next I
wsh.Cells(Row,3).Value = wsh.Cells(Row,3).Value + StudRem
Next Row
End Sub

Please note, this macro assumes that the macro is within the same workbook
as the allocation of the students between periods. There's a few things in
this macro that is not needed, but still good programming practice as there
are some programs that requires such strict adherence not to mention that it
also allows for knowing where and how things works more easily. This has
minimal error checking in it as you may want to put in more, but that's up
to you. Also, for the remaining students that didn't divide equally, I put
the remaining ones in the first period. For example, if you had 4 periods,
and you had 55 students, the first period would have 13 students while each
of the remaining 3 periods would have 14 students each based on how this
macro is setup.

Just as a side comment, I have went to a school within a school district
that has 6 grading periods per year. (Yes, 3 per semester, so instead of
getting graded twice, I was actually graded 3 times per semester in that
case. High school would have those 3 and Semester Exams per semester for
each of their 6 courses that a student would take up per semester.)
--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"kevindmorgan" <kevindmorgan.26f1yo_1145290203.3569@excelforum-nospam.com>
wrote in message
news:kevindmorgan.26f1yo_1145290203.3569@excelforum-nospam.com...
>
> Who's up for a challenge? I am sure someone is smart enough to figure
> this out. I have a way of doing it, but I am sure there is an easier
> way.
>
> Bottom line up-front. I need to separate students so that there are
> roughly equal numbers of students over 1-5 periods. The reason I say
> "roughly" is because there is a catch. The number of students
> per-period needs to be an even number of students when possible. For
> example:
>
> 16 students, 4 periods...the desired result would be 4 students in each
> of the 4 periods. That's simple, but...
>
> If there were 52 students, the result could not be 13 students in each
> of the 4 periods, since they should be even numbers when possible. It
> would be 14,12,14,12 or similar. (12,14,14,12, etc.)
>
> At the time this process takes place, the TOTAL number of students per
> class is already in one column...say Column A. The number of periods
> (from 1-5) is also known at this point. Let's say this number is in
> column B.
>
> In columns C,D,E,F,G (representing periods) would be filled with the
> student numbers (or a zero if none are in the period).
>
> The student numbers can be anywhere from 2-60, but I'd think a solution
> would work with any number of students.
>
> If anyone finds a solution for this, you are indeed worthy of my
> praise!
>
>
> --
> kevindmorgan
> ------------------------------------------------------------------------
> kevindmorgan's Profile:
http://www.excelforum.com/member.php...o&userid=32232
> View this thread: http://www.excelforum.com/showthread...hreadid=533452
>

[Ronald Dodge]

I initially started to think this way, but as I read his example, I realized
what all he was looking for, something that would be about as evenly spaced
out as possible, and with the higher and lower number of students per class
is about evenly spaced out. The only thing that could create a minor
problem is that it puts the extras in later rather than earlier, which could
potentially create a scheduling conflict, but for the purpose of this
exercise, that part is left out as we don't have enough info to make that
decision. This type of exercise is very similar to how things would be done
in a production scheduling type environment, if it's reasonably possible to
do, but then again, it also depends on customer demands too.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:865AD220-3F64-45F0-BD75-F821755B0B56@microsoft.com...
> Pleased be advised that this comes from the math challenged:
>
> 1. divide the number of students by the number of classes and round down
to
> the next integer
>
> 2. if the result is an odd number, then reduce it by one.
>
> 3. assign this number to each class except the last class
>
> 4. assign to the last class the number of students needed to complete the
> allocation
>
>
> This method will result in all the classes having equal numbers (except
> possibly the last) and all the classes except possibly the last having an
> equal number of students.
> --
> Gary''s Student
>
>
> "kevindmorgan" wrote:
>
> >
> > Who's up for a challenge? I am sure someone is smart enough to figure
> > this out. I have a way of doing it, but I am sure there is an easier
> > way.
> >
> > Bottom line up-front. I need to separate students so that there are
> > roughly equal numbers of students over 1-5 periods. The reason I say
> > "roughly" is because there is a catch. The number of students
> > per-period needs to be an even number of students when possible. For
> > example:
> >
> > 16 students, 4 periods...the desired result would be 4 students in each
> > of the 4 periods. That's simple, but...
> >
> > If there were 52 students, the result could not be 13 students in each
> > of the 4 periods, since they should be even numbers when possible. It
> > would be 14,12,14,12 or similar. (12,14,14,12, etc.)
> >
> > At the time this process takes place, the TOTAL number of students per
> > class is already in one column...say Column A. The number of periods
> > (from 1-5) is also known at this point. Let's say this number is in
> > column B.
> >
> > In columns C,D,E,F,G (representing periods) would be filled with the
> > student numbers (or a zero if none are in the period).
> >
> > The student numbers can be anywhere from 2-60, but I'd think a solution
> > would work with any number of students.
> >
> > If anyone finds a solution for this, you are indeed worthy of my
> > praise!
> >
> >
> > --
> > kevindmorgan
> > ------------------------------------------------------------------------
> > kevindmorgan's Profile:
http://www.excelforum.com/member.php...o&userid=32232
> > View this thread:
http://www.excelforum.com/showthread...hreadid=533452
> >
> >

[kevindmorgan]

I think the term "class" threw me off at first. If you mean each period, it won't work that way. If the total students were 52, then the periods would be broken down (12,12,12,16). It would need to be combinations of 12 or 14 students per period.

If you really did mean each "class", the class sizes are pre-determined and can't change. Each class is broken down into either 1-5 periods.

I didn't think I'd get many "bites" on this one. Another way of explaining this:

Given two numbers. (x and y)
X= number of students
y= number of available periods

Get P1,P2,P3,P4,P5 (Y will determine how many "P" numbers to return)

divide the students into (y)-number of periods where the students in each period is an even number (when possible) AND the students per period is as evenly distributed as possible.

Examples:

Given 16 students, 2 periods:
Solution: 8 in each

Given 16 students, 3 periods:
Solution: Period 1 & 2 have 6 students, P3 has 4 students.

Given 16 students, 4 periods:
Solution: All 4 periods have 4 students

Given 16 students, 5 periods:
Solution: 3 periods of 4 students, 2 periods of 2 students

[kevindmorgan]

Well, that was some work done!

In my case, as you can tell from my previous post, has a set number of students per class. The "Periods" are during the day. P1 from 6AM-8AM, P2 from 8AM-10AM, etc.

Thanks for your efforts. I am over here working on another myself!

[QUOTE=Ronald Dodge]So you want to have a numbre of students that's divisible by 2. What if you
have an odd number of students over the course of the year, say 45 students?
What do you do then?

Here's one possible solution:

To start off, we will need to divide the total number of students over the
course of the year by both the number of periods and the divisor that you
are wanting to use, which in this case is 2.

[Ronald Dodge]

I spotted an issue within my own code, which needs to be modified slightly,
but still quite similiar. The issue wasn't the spacing part, but it was
putting an incorrect number of extras into the first period or class. Here
it is.

Sub StudentAlloc(StudDiv as Long)
Dim I as Long, MinPerPeriod as Long, StudMod as Long, Periods as Long, Row
as Long, TotalDiv as Long
Dim StudTotal as Long, StudRem as Long, wsh as Worksheet, RemXtrStud as
Long
Set wsh = Thisworkbook.Worksheets("StudentAllocation") 'This assumes the
information is stored on the worksheet named "StudentAllocation".
FirstRow = 5 'Assuming the data actually starts on Row 5.
If wsh.Range("A65536").End(xlUp).Row > 4 Then
LastRow = wsh.Range("A65536").End(xlUp).Row
Else
MsgBox "There's no data available to process this code.",48
Exit Sub
End If
wsh.Range("C" & CStr(FirstRow) & ":G" & CStr(LastRow)).ClearContents
For Row = FirstRow to LastRow Step 1
StudTotal = wsh.Range("A" & CStr(Row)).Value
Periods = wsh.Range("B" & CStr(Row)).Value
TotalDiv = Periods * StudDiv
MinPerPeriod = StudTotal\TotalDiv 'Note, this is an integer div
operator, not the normal div operator.
StudMod = StudTotal Mod TotalDiv
RemXtrStud = StudMod
StudRem = 0
For I = 3 to Periods+2 Step 1
If StudRem + StudMod >= TotalDiv Then
StudRem = StudRem + StudMod - TotalDiv
wsh.Cells(Row,I).Value = MinPerPeriod * StudDiv + StudDiv
RemXtrStud = RemXtrStud - StudDiv
Else
StudRem = StudRem + StudMod
wsh.Cells(Row,I).Value = MinPerPeriod * StudDiv
End If
Next I
wsh.Cells(Row,3).Value = wsh.Cells(Row,3).Value + RemXtrStud
Next Row
End Sub

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"kevindmorgan" <kevindmorgan.26faj1_1145301307.3199@excelforum-nospam.com>
wrote in message
news:kevindmorgan.26faj1_1145301307.3199@excelforum-nospam.com...
>
> Well, that was some work done!
>
> In my case, as you can tell from my previous post, has a set number of
> students per class. The "Periods" are during the day. P1 from 6AM-8AM,
> P2 from 8AM-10AM, etc.
>
> Thanks for your efforts. I am over here working on another myself!
>
> Ronald Dodge Wrote:
> > So you want to have a numbre of students that's divisible by 2. What if
> > you
> > have an odd number of students over the course of the year, say 45
> > students?
> > What do you do then?
> >
> > Here's one possible solution:
> >
> > To start off, we will need to divide the total number of students over
> > the
> > course of the year by both the number of periods and the divisor that
> > you
> > are wanting to use, which in this case is 2.
>
>
> --
> kevindmorgan
> ------------------------------------------------------------------------
> kevindmorgan's Profile:
http://www.excelforum.com/member.php...o&userid=32232
> View this thread: http://www.excelforum.com/showthread...hreadid=533452
>

[Gary''s Student]

Hi Ronald:

Could a croupier technique be used? Deal out students two at a time until
they are completed distributed:

Sub dealer()

Application.ScreenUpdating = False
students = Cells(1, 1).Value
classes = Cells(2, 1).Value
pairs = Round(students / 2, 0)
check = students - pairs * 2

If check > 0.5 Then
extra = 1
Else
extra = 0
End If

Columns("B:B").Select
Selection.Clear

j = 1
For i = 1 To pairs
Cells(j, 2).Value = Cells(j, 2).Value + 2
j = j + 1
If j > classes Then
j = 1
End If
Next

Cells(j, 2).Value = Cells(j, 2).Value + extra
Application.ScreenUpdating = True
End Sub

--
Gary''s Student


"Ronald Dodge" wrote:

> I initially started to think this way, but as I read his example, I realized
> what all he was looking for, something that would be about as evenly spaced
> out as possible, and with the higher and lower number of students per class
> is about evenly spaced out. The only thing that could create a minor
> problem is that it puts the extras in later rather than earlier, which could
> potentially create a scheduling conflict, but for the purpose of this
> exercise, that part is left out as we don't have enough info to make that
> decision. This type of exercise is very similar to how things would be done
> in a production scheduling type environment, if it's reasonably possible to
> do, but then again, it also depends on customer demands too.
>
> --
> Ronald R. Dodge, Jr.
> Production Statistician
> Master MOUS 2000
>
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:865AD220-3F64-45F0-BD75-F821755B0B56@microsoft.com...
> > Pleased be advised that this comes from the math challenged:
> >
> > 1. divide the number of students by the number of classes and round down
> to
> > the next integer
> >
> > 2. if the result is an odd number, then reduce it by one.
> >
> > 3. assign this number to each class except the last class
> >
> > 4. assign to the last class the number of students needed to complete the
> > allocation
> >
> >
> > This method will result in all the classes having equal numbers (except
> > possibly the last) and all the classes except possibly the last having an
> > equal number of students.
> > --
> > Gary''s Student
> >
> >
> > "kevindmorgan" wrote:
> >
> > >
> > > Who's up for a challenge? I am sure someone is smart enough to figure
> > > this out. I have a way of doing it, but I am sure there is an easier
> > > way.
> > >
> > > Bottom line up-front. I need to separate students so that there are
> > > roughly equal numbers of students over 1-5 periods. The reason I say
> > > "roughly" is because there is a catch. The number of students
> > > per-period needs to be an even number of students when possible. For
> > > example:
> > >
> > > 16 students, 4 periods...the desired result would be 4 students in each
> > > of the 4 periods. That's simple, but...
> > >
> > > If there were 52 students, the result could not be 13 students in each
> > > of the 4 periods, since they should be even numbers when possible. It
> > > would be 14,12,14,12 or similar. (12,14,14,12, etc.)
> > >
> > > At the time this process takes place, the TOTAL number of students per
> > > class is already in one column...say Column A. The number of periods
> > > (from 1-5) is also known at this point. Let's say this number is in
> > > column B.
> > >
> > > In columns C,D,E,F,G (representing periods) would be filled with the
> > > student numbers (or a zero if none are in the period).
> > >
> > > The student numbers can be anywhere from 2-60, but I'd think a solution
> > > would work with any number of students.
> > >
> > > If anyone finds a solution for this, you are indeed worthy of my
> > > praise!
> > >
> > >
> > > --
> > > kevindmorgan
> > > ------------------------------------------------------------------------
> > > kevindmorgan's Profile:
> http://www.excelforum.com/member.php...o&userid=32232
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=533452
> > >
> > >
>
>
>

[kevindmorgan]

Very nice. I think I am going to go with this technique. I have noticed that in some cases it doesn't add up (try 3, 7, 11, or 15 students with 4 periods) but I can modify it.

I am also going to "deal" the numbers to an array to speed things up rather than do it on the sheet, but I owe the technique to you!

Thanks very much.

Kevin

[QUOTE=Gary''s Student]Hi Ronald:

Could a croupier technique be used? Deal out students two at a time until
they are completed distributed:

Sub dealer()

Application.ScreenUpdating = False
students = Cells(1, 1).Value
classes = Cells(2, 1).Value
pairs = Round(students / 2, 0)
check = students - pairs * 2

If check > 0.5 Then
extra = 1
Else
extra = 0
End If

Columns("B:B").Select
Selection.Clear

j = 1
For i = 1 To pairs
Cells(j, 2).Value = Cells(j, 2).Value + 2
j = j + 1
If j > classes Then
j = 1
End If
Next

Cells(j, 2).Value = Cells(j, 2).Value + extra
Application.ScreenUpdating = True
End Sub

--
Gary''s Student

TE]