## Classroom organisation

373 Views 12 Replies Latest reply: Feb 28, 2013 5:22 AM by Jerrold Green1
Level 2 (160 points)
Currently Being Moderated
Feb 26, 2013 1:46 AM

Hi,

I am currently trying to organize pupils into their classes from September. It is proving a substantial challenge this year, and I am hoping a Numbers table would ease my life. I am hoping though that someone can help me create it!!

I have 10 teachers to split 300 pupils. I need to balance the classes by gender and age ability etc.

Basically I would like to allocate a child to a teachers class, and have a visible count (in a seperate summary table if possible) of how many boys and girls I am assigning to each teacher's class. If there was any way to color code the child once I have allocated a class it would be visually easier.

Gwyn

MacBook Pro, Mac OS X (10.7.2)
• Level 7 (27,470 points)
Currently Being Moderated
Feb 26, 2013 5:59 AM (in response to Gwyn Plem)

Welcome back, Gwen,

The key to this question is similar to the subject of your last one - Countif.

Here's an example of what you might do:

I'll give you he expressions in the Column D Header Cells first:

D1:  =COUNTIF(D, TRUE )

D2: =COUNTIFS(\$B, "F", D, TRUE)

D3: =COUNTIFS(\$B, "M", D, TRUE)

D4: =IFERROR(AVERAGEIF(D, TRUE, \$C), "-")

Select that group of cells and Fill Across to the Right.

If you want other statistics, you can study the expressions and modify them to get your preferences.

N6 is the first of a set of Countif statements that counts the number of checked boxes for each student. It is Conditionally formatted to set the color to orange if the student isn't assigned yet, green if assigned and red if assigned more than once.

Jerry

• Level 6 (12,000 points)
Currently Being Moderated
Feb 26, 2013 7:03 AM (in response to Gwyn Plem)

Gwen,

This may be a good start:

- Create a table titled "Student List" where you have three (or more) columns titled "ID", "Name" and "Gender"

- Create a table titled "Teacher List" where you have Five (or more) columns titled "ID", Name", "Total", "Girls" and "Boys"

- Create a table titled "Master List" where you have six (or more) columns titled "Student ID", "Already Included", "Student Name", "Gender", "Teacher ID", and "Teacher Name"

For the table "Student List" you have to fill in this information.

For the table ""Teacher List you have the fill in the first two columns.

For the table "Master List" enter the formulas as follows (Master list is rught-most table):

C2=IF(ISBLANK(A2), "", VLOOKUP(A2,Student List :: A:C, 2, 0))

D2=IF(ISBLANK(A2), "",VLOOKUP(A2,Student List :: A:C, 3, 0))

F2=IF(ISBLANK(E2), "",VLOOKUP(E2, Teacher List :: A:B, 2, 0))

You fill in columns A and E

select cells B2 thru D2 and fill down as needed

select F2 and fill down as needed

For the table "Teacher List":

C2=SUM(D2:E2)

D2=COUNTIFS(Master List :: D, "=FEMALE", Master List :: E, "="&A2)

E2=COUNTIFS(Master List :: D, "=MALE", Master List :: E, "="&A2)

select C2 thry E2 and fill down as needed

To shade the Teacher table I suggest two shader tables (on as wide as columns A, B and C of the Teacher list, and the other as wide as columns D and E.

The shader table on the left (mostly red) is titled "Teach Shader" and is set up like:

A2=COUNTA(Student List :: A)/COUNTA(Teacher List :: A)-Teacher List :: C2

select A2 and fill down as needed (it should have the same number of rows as the yeacher list

now select A2 thru A11 and open the conditional format rules and set up like:

The student shader is on the right (mostly green) and is set up like:

A2=ABS(Teacher List :: D2-Teacher List :: E2)

select A2 and fill down as needed.  Apply similar shading as in the teacher shader.

now select both shader tables and send to back.  Selec the teacher List and make the fill color "none":

now select the two shader tables and slide them under the "Teacher List" and align.

It should look like this when complete:

• Level 7 (27,470 points)
Currently Being Moderated
Feb 26, 2013 7:28 AM (in response to Gwyn Plem)

Gwen,

I realized on rereading my suggestion that I never got around to giving you the formula for the E column, the number of assignment entries for each student. That column is just there to remind you of any neglected students and to alarm you if you make a double-assignment. The formula in N6 is:

=COUNTIF(D6:M6, TRUE)

Fill Down to your last student's row.

I know you could have figured out that one on your own!

Here's the conditional format for the E cells:

Good luck with your latest project.

Jerry

• Level 7 (27,470 points)
Currently Being Moderated
Feb 27, 2013 4:22 AM (in response to Gwyn Plem)

Gwyn,

Please send me the file in your screen shot, and I'll send it back to you in working condition, and I'll post the explanation of what I did here. I have a solution in mind, but it will be much easier if I don't have to recreate your layout.

You can't average your format for age, it's a string, not a duration value, and that's the rub. I will leave your display as is, and add a hidden column with a duration that is average-friendly.

Jerry

• Level 7 (27,470 points)
Currently Being Moderated
Feb 27, 2013 8:58 AM (in response to Gwyn Plem)

You can grab the revised document here.

I added a column to calculate the age of each student in days, Duration. This averaged with no problem.

The expression for Age (Days) is:

=TODAY()-C where C is your DOB column.

The average age calculation for Teacher 1 is:

=DATEDIF(TODAY()-AVERAGEIF(F, TRUE, \$D), TODAY(), "Y")&"y "&DATEDIF(TODAY()-AVERAGEIF(F, TRUE, \$D), TODAY(), "YM")&"m"

If you could tolerate the average age display in simple Weeks or Days, it would be a much shorter expression, but if you don't mind the overhead, it's not a problem.

Jerry

• Level 7 (27,470 points)
Currently Being Moderated
Feb 27, 2013 2:44 PM (in response to Gwyn Plem)

Gwyn,

I added a column in the Main table for a Class-Student identifier. I collected the ages by class (teacher) in an auxiliary table using a lookup of the Class-Student ID.

Here's the modified Main table:

The Column E expression is:

=RIGHT("0"&MATCH(TRUE, G6:O6), 2)&"-"&RIGHT("0"&COUNTIF(OFFSET(\$F\$5, 0,MATCH(TRUE, G6:O6), ROW()-4),TRUE), 2)

The Age Range expression for Teacher 1 is:

Ages by Class :: B44 a simple reference to the result in the Aux table.

The Aux Table "Ages by Class" looks by this:

The expressions in the Teacher 1 columns are as follows.

Age in days:

=IFERROR(INDEX(Main, MATCH(RIGHT("0"&COLUMN()-1, 2)&"-"&RIGHT("0"&ROW()-1, 2), Main :: \$E,0), 4), "")

Min:

=MIN(B)

Max:

=MAX(B)

Range (Weeks):

=DUR2WEEKS(B43-B42)

You can pick up a copy here.

As I wrote it, the document will handle up to 99 classes and up to 99 students per class.

Regards,

Jerry

• Level 7 (27,470 points)
Currently Being Moderated
Feb 28, 2013 5:22 AM (in response to Gwyn Plem)

Gwyn,

It's a pleasure to work with you on your well-written problem statements that have obviously practical application. I'll leave the documents on the Dropbox site for about a week for anyone who whant to see the solution in full context. After that I will remove them to recover the storage space.

Jerry

#### More Like This

• Retrieving data ...