Data Portfolio

Explore

Projects & Products

At Risk of Failure

User-Facing:
See the report here
⁠
⁠

This is an automated report that identifies students in danger of failing for the school year as well as the grade they need for each course to pass for the year. Students are defined as “on the cusp” if their grade in a particular course is such that if they fail for the quarter, they fail for the school year.

Grades by student and course are separated into three priority areas for cohort teams to target interventions

graphic summaries for schoolwide leadership to support scrum sprints and provide differentiated support

Automatically generated letters to students and home are created from this report as well

Overview Dashboard

⁠

Cohort Filtered View

⁠

In this view, we have a sheet for each cohort in the workbook. Populated are lists of students in that cohort who are separated by priority level.

Priority 1: Students “on the cusp” in more than one course

Priority 2: Students “on the cusp” in one course only

Priority 3: Students who must maintain their grade and pass the final to pass for the school year

To the left is a sample of the list of students who are in the priority 1 category alongside the courses in which they are “on the cusp” as well as their current grade and quarter 4 grade needed to pass the course (assuming a 50 on the final).

By pulling out these lists, cohort-based leadership can target conferences with students by priority to communicate and monitor student progress toward goals.

Priority Course View by Content

⁠

This view populates a list of teachers by content area sorted descending by number of students who are on the cusp in their courses. Content leads can then utilize this list to target support for particular teachers to maximize high-leverage impact for students to meet their achievement goals.

Automated Letters

⁠

Loading…

⁠

In addition, a mail merge was run from a hidden sheet in the workbook to automatically generate letters for each student listing each course for which they are “on the cusp” of passing, with their current yearlong average and the grade needed to pass the course.

These letters were printed out by cohort and distributed to support student motivation, self-management, and goal-setting to ensure they are aware of the marks they needed in each course to pass that course.

Backend & Construction

The workbook is set up with the following sheets:

semester_1 student grades by course, teacher, content, and cohort

quarter_3 student grades by course, teacher, content, and cohort

Calculator which lists all unique students and courses with...

the semester_1 and quarter_3 grades populated using a simple vlookup

calculation to determine current average

calculation to determine the grade needed for quarter 4 under different assumptions for final grades

calculation to determine whether that student’s grade in that course is “on the cusp” which is defined as any student who needs to pass Quarter 4 and their final exam to pass for the school year

calculation to determine the specific grades necessary to pass that course

Priority which lists all unique students and courses and...

formulaically filters the results from Calculator to only student grades which meet the criteria for “on the cusp”

determines a students priority level by:

the number of courses that a student is currently failing and must pass to earn the course credit

the number of courses that a student is currently passing but still must pass to earn the course credit (coded as maintenance only)

Summary Helper which summarizes all data to identify numbers by cohort, by content, by teacher, and by course.

Sample code:

=query(by_teacher!$F$3:$I,"select G,F,H where I matches '"&B2&"' and H>5 order by H desc,G desc")

References

G → Teacher

F → Course

H → Number of students on the cusp

I → Content area

'"&B2&"' → a cell that contains the text value of the content area allowing this formula to be simply pasted into additional columns for other contents

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.