Help a Teacher!

[efitzgerald]

Hello!

I am trying to create a writing rubric for my team of teachers to use and I am stuck! Basically, there is a list of requirements for students to reach a level. For some levels, students have to meet all the standards, for others, they have to meet 5 of the standards. If, for example, they only meet 2 out of the 3 requirements to be a level 2, then they are still considered to be a level 1. I am also trying to calculate how many students are passing each standard so that the teacher can pinpoint what she should teach during writing because it is weak on the rubric. As you will see in my attachment, I"m on a path, but it's not quite there. I also am not sure that it is best to use different numbers...maybe just 1 or Y/N? Any help that anyone could offer would be greatly appreciated throughout our school!

Thank you!!
Elaine
Early Writing Continuum Tracker.xlsx

[SamT]

Elaine,

With no column headings in columns C thru Q, I can't even guess at their purpose.

It seems to me that you are trying to put too much information on one sheet.

For example, I am sure that you want to enable a teacher to track success more frequently than 3 times a year. For that purpose I personally would have a sheet with dates across the top and a copy of Columns A and B from your attachment in the first two columns. In the Columns below the dates, I would enter the number of students who "graduated" to the Level in that level's row.

To track each students rate of progress, I would, on another sheet, list the students in Column A, their current level in Column B, and again put dates across the top. merely place an X under the date when the next level of reading is met. Then formulas in Column B could be =CountA(Cn:ZZn) + 1. Plus 1 since all students start at level 1, (or place a X in each students Column C)

Finally, you would need a Summaries Sheet which can have Tables, Pivot tables, Charts and any number of other Excel BI functions.

The only Macros I see needed are for the tedious task of entering the dates each semester and auto-scrolling each Sheet to put the current date column to the left. A macro to enter the Number of "graduating" students from the Student listing sheet to the Levels attained sheet would be nice to have

I hope this helps.
SamT

ps: Don't be fooled by my avatar, I have thousands of post on other forums (|:>)

[WizzardOfOz]

Elaine,

I agree with Sam. You are making it way too complicated. I subscribe to the KISS principal, keep it simple stupid. In principal you could use complicated array formulas to achieve this but you need to remember to press Control Shift Enter all the time and sooner or later someone is going to mess it up.

Therefore move all the calculations into a grading sheet
simple SUMIF formula =SUMIF(Marks!$A:$A,Grades!$A2,Marks!C:C) for each grade and student

Then for every grade/student have they passed into the next grade
=IF(C2>=$B2,$A2+1,IF(C2>0,$A2-1+C2/$B2,0))

Finally I noticed in your example that the junior grades do not have to be filled in so back to the student sheet
=MAX(Grades!C20:C27)

Simple and should be manageable to maintain.
At the end of every semester I would simply copy the old to a new sheet (e.g. Fall) and have another sheet to combine the four seasons.