Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Analyze Student Attendance Data

Copper Contributor

# Analyze Student Attendance Data

I have a very large spreadsheet with over 15K rows.  The spreadsheet is an export from our student information system of student attendance data for the second reporting term (10/11/23 - 12/21/23).  In the spreadsheet, you'll find the attendance code for each day of the term for each student.  My objective is to calculate the attendance rate for each student and then calculate an attendance rate for each home room.  The math is simple, my problem is figuring out how to organize the data.  I need to count the total days the student was in attendance and divide by the total number of days enrolled.  Then I need to average that percentage for each home room.  Linked below is my spreadsheet.  Attendance Data.xlsx

Any help would be appreciated.  Thank you.

6 Replies

# Re: Analyze Student Attendance Data

What do the attendance codes mean?

Which one(s) should we include to measure attendance?

What is the home room? The grade level?

# Re: Analyze Student Attendance Data

IA, IAV = in attendance

EXC, UEX, MEDEX = absent

Column E is the homeroom teacher.

# Re: Analyze Student Attendance Data

Thanks! Does this do what you want?

# Re: Analyze Student Attendance Data

Unfortunately, it does not. Looking at student 11071, they were in attendance for 34 out of 42 days. Their attendance would be 81%.
Each homeroom attendance should calculate a unique value based on the student attendance. Column L in your example is the value I'm looking for.

# Re: Analyze Student Attendance Data

@Rockiesfan My apologies, stupid mistake on my side.

The formula in I2 should be

=COUNTIFS(\$A\$2:\$A\$15078,H2,\$C\$2:\$C\$15078,"IA*")/COUNTIFS(\$A\$2:\$A\$15078,H2)

Fill down from there.

# Re: Analyze Student Attendance Data

Perfect! Thank you so much. Now if I can only repeat your steps for the next quarter. :)