Primary Teacher Needs Help please

[mendle]

Hi
I am hoping that someone can please help me.
Our schools spends hours every year calculating how many students should go in each class. We are given teachers on the ratio of students to teachers. Unfortunately, the students never fall evenly in the classes so we have to put some students from different grades together and form a composite class. Different grades have different numbers. It is preferable to have as fewer composites as possible

Kindergarten 20 - 1 teacher
Year 1 22 - 1 teacher
Year 2 24 - 1 teacher
Year 3 30 - 1 teacher
Year 4 30 - 1 teacher
Year 5 30 - 1 teacher
Year 6 30 - 1 teacher

This year we have students in these classes

Kindergarten 71
Year 1 89
Year 2 50
Year 3 62
Year 4 62
Year 5 70
Year 6 68

Is it possible to make a generator to calculate the numbers? It would be great to have the facility to regenerate the numbers if they don't look right the first or second time.
Would this be a difficult thing to do? Could anyone please set me on the right track to get started. I would be so grateful for any help that I could get. Would this type of number "cruncher" have a special name? Thank you

 

[kvsrinivasamurthy]

For kinder garten there are 71. 3 teachers get 20 each and 4th teacher gets 11.You want distribution like that or evenly distribute 71 to 4 teachers.

 

[bs0d]

I'd think you would want to keep things as as simple as possible.

First column - List the S:T ratio for each grade.
Next column - List students by grade.
Next column - Calculate teachers by using rounddown() function. =rounddown(students/st ratio, 0)
Next column - show students without a teacher via: =students - (st ratio * teachers)
Last column is where you build the composite classes. Group remaining students as desired by certain grades. You don't specify full criteria for building them, and you mention regenerating so there may be some subjectivity to it.

Using the example you provided, and following the instructions I outlined above, I calculate 3 composite teachers via:
* K + 1 teacher with 22 students ([5] k, [17] 1st).
* 2 + 3 teacher with 22 students ([20] 2nd, [2] 3rd)
* 4-5-6 teacher with 20 students ([2] 4th, [10] 5th, [8] 6th)