Due in Three Hours/EXCEL SHEET

Analyzing School Attendance Data

Socrates School District is concerned about the number of students that are missing school. All teachers

take daily attendance so the district has relatively reliable statistics for the students that are not in

attendance on a particular day. Basic demographic information such as grade level, race, GPA and

ethnicity is available for those students. There are some thoughts on the School Board that students

who perform better in school are absent less often. Others on the board feel that absenteeism is related

to race; students with a particular racial background are more prone to miss school than other racial

groups. There were even some questions about the months where more students miss school. For

example, is November a high absentee month? You have been assigned the task of analyzing this

spreadsheet data in the hopes of finding some trends that may explain the absenteeism. Your results

and analysis will be reported at the next School Board meeting.

Use the Excel file, StudentAttendanceData.xlsx, to answer the following questions. The file contains two

worksheets: 1) Absent Student Information and 2) Absences Data.

1. How does the absentee rate (absences/students) compare to the student’s GPA? Count the number

of students with a GPA greater or equal to 80 and the absences for students with a GPA greater or equal

to 80. Calculate the absentee rate for these students. Use the same process to calculate the absentee

rate for those students whose GPA is less than 80. Compare the two ratios. What do you conclude?

2. In each grade level, is the absenteeism the same for all races? Compare the absenteeism for every

grade level and every race. A pivot table will help you to answer this question. Use the Absent Student

Information worksheet, and place race on the columns and std_grade_level on the rows. The

summation function displays the sum of the entries for each grade level. This pivot table can be placed

on a new worksheet.

Create two charts from the pivot table created in #2. These can be placed on the same worksheet. The

first chart should be a Line with markers. The second chart should be a 100% stacked in 3D. Be sure to

label each chart. What trends do you notice?

3.What is the racial composition of those absent each month? Compare the absenteeism for every

month and every race. You’ll need to modify the Absences Data worksheet to answer this question.

Create a new column, Month, from the att_data column. This column uses the Month function. Insert a

pivot table that uses Month for the rows and race for the columns. The summation function displays the

sum of the entries for every month. Place this on a new worksheet.

On the same worksheet, create a Line with Markers chart to display these findings. Be sure to label the

chart. What do you observe?

4. Create a report in Word that presents your findings. Be sure to answer the three questions posed. In

the report include the three charts made from the two pivot tables. You can include any other charts

that will support your findings.

5. Submit your modified Excel workbook and your Word document through the Attach File feature.