Teacher's Gradebook

In this exercise you are going to create a teacher's grade book. A teacher grades his students according to 3 factors: Participation during class, project and final exam. The final grade is calculated using the following formula

Final Grade = Participation * w1 + Project * w2 + Exam * w3

The w1, w2 and w3 values are the weights for each factor and they determine how much importance the teacher gives for a factor to the calculation of the final grade.

The grade book must also give an indication PASS or FAIL for each student. To pass the class the grade must be >= 5. Finally the grade book calculates and displays some statistics for the class such as the total students, class grade average, minimum and maximum grade, how many students have passed the class and the success rate.

Instructions

1. Open the file teacher-gradebook-start.ods. You will work on this document. You must type all formulas inside the cells highlighted with yellow color.

2. Calculate the Final Grade. The equation is giver for you as

Final Grade =D5*C$25+E5*C$26+F5*C$27

Then use Autofill to fill the remaining cells.

3. Display the PASS or FAIL text. Use the IF function to check if the final grade is greater or equal than 5. Use Autofill to fill the remaining cells

4. Count the total number of students. Use COUNT function to count the cells that contain the Last Name.

5. Calculate the class statistics:

• Class Average. Use AVERAGE function to calculate the average of all Final Grades
• Min and Max Grade. Use MIN and MAX function to fin the minimum and maximum of Final Grades.
• PASS. The total number of students that passed the class. Use COUNTIF to count the grades that are larger or equal to 5. Use the formula: =COUNTIF(G5:G20; ">=5")
• Success Rate = Students that PASSED / Total Students.

6. Change the appearance of the spreadsheet. Format data, resize columns and rows, apply character formatting, background colors and borders.

7. Save your file and submit