Spreadsheet Grade Calculation
A teacher has a spreadsheet with student grades. Columns: A (Student Name), B (Homework 1), C (Homework 2), D (Homework 3), E (Exam). The final grade is computed as: Homework average (average of B, C, D) weighted 40%, and Exam weighted 60%. The teacher wants to assign grades: 90-100 = 'A', 80-89 = 'B', 70-79 = 'C', below 70 = 'F'. They also want to highlight students who scored below 60 on the exam. The spreadsheet has data for 30 students in rows 2 to 31.
1. Write a formula for cell F2 (Final Grade) that computes the weighted average, assuming homework average is calculated as AVERAGE(B2:D2).
Read the worked answer
Formula: =0.4AVERAGE(B2:D2)+0.6E2. Alternatively: =AVERAGE(B2:D2)0.4+E20.6. This calculates the homework average, multiplies by 0.4, adds exam score times 0.6.
2. Write a formula for cell G2 (Letter Grade) that assigns the letter grade based on the final grade in F2.
Read the worked answer
Use nested IF: =IF(F2>=90,"A",IF(F2>=80,"B",IF(F2>=70,"C","F"))). This checks from highest to lowest. Alternatively, use IFS if available: =IFS(F2>=90,"A",F2>=80,"B",F2>=70,"C",TRUE,"F").
3. How can the teacher highlight students who scored below 60 on the exam? Describe the steps using conditional formatting.
Read the worked answer
Select the range E2:E31. Go to Conditional Formatting > New Rule > Format only cells that contain. Set rule: Cell Value less than 60. Choose a fill color (e.g., red). Click OK. This will highlight exam scores below 60. Alternatively, use a formula: =$E2<60 applied to the range.