Excel Spreadsheet Grade Book Spreadsheet

Excel Spreadsheet

Grade Book

Spreadsheet

Grade book is table with 10 columns:

Column a Student ID (chosen by student)

Column B. Student Name (hidden for privacy reasons)

Column C. Student Last Name (hidden for privacy reasons)

Column D. Attendance (calculated as total number of hours attended / total class hours)

Column E. Final Grade (in letter notation)

Column F. Final Grade (as mean from grade for homework, oral, written quizzes and attendance)

Column G. Total Grade for oral quizzes

Column J. Total Grade for written quizzes

Column H. Total Grade for homework

Column I Total number of hours attended (max 60)

Total number of students in class

Calculations:

Calculations for attendance are provided in cells D3:D30

Calculations for final grade are provided in cells F3:F30

Calculations for final grade in letter notation were done by logic functions if:

If data from cells F3:F30 is greater than 0.95 than grade is "A"

If data from cells F3:F30 is greater than 0.89 than grade is "A-"

If data from cells F3:F30 is greater than 0.85 than grade is "B"

If data from cells F3:F30 is greater than 0.79 than grade is "B-"

If data from cells F3:F30 is greater than 0.75 than grade is "C"

If data from cells F3:F30 is greater than 0.69 than grade is "C-"

If data from cells F3:F30 is greater than 0.65 than grade is "D"

If data from cells F3:F30 is greater than 0.59 than grade is "D-"

If data from cells F3:F30 is equal or smaller than 0.59 than grade is "F"

Logic formulas for these calculations are provided in cells: E3:E30

Spreadsheet 2

Final grade results chart

Provides final grade distribution, data was collected using formula of data summing by selected criteria (cells D3:D11)

Written quizzes results chart

Provides written quizzes results distribution, data was collected using data summing by selected criteria (cells K3:K8)

Oral quizzes results chart

Provides oral quizzes grade distribution, data was collected using data summing by selected criteria (cells K36:K41)

Homework results chart

Provides homework results distribution, data was collected using data summing by selected criteria (cells D36:D41)

Correlation of attendance to final grade graph

This graph was made in order to understand dependence between attendance and performance of students. We can see that there exists a liner correlation, which means that attendance has a direct impact on final grade of students.

In order to built this function data from columns D3:D30 (attendance) and data from columns F3:F30 (final grade) from Spreadsheet 1 was tested using Linear regression formula of excel.