Challenge Scenario
Teachers and course instructors often need to calculate final student grades at the end of a semester. In this gradebook, you have a list of students with their Midterm Score (Column B) and Final Exam score (Column C).
To determine if each student passes the course, the school has a simple grading policy: any student whose overall average score is 70 or higher earns a "PASS", while an average below 70 receives a "FAIL".
As the Course Coordinator, your job is to build the gradebook calculations:
- Average Score (Column D): Calculate the average between the Midterm and Final Exam scores using
AVERAGE.
- Academic Standing (Column E): Automatically assign
"PASS" if the average is 70 or more, otherwise "FAIL" using IF.
- Class Summary (Cells B8 & C8): Find the overall class average and count how many students passed the course.
Your Sheet Layout
A well-organized gradebook separates the original test scores from the calculated grades and overall class summary. Here is how this sheet is arranged:
This worksheet is organized into three simple zones:
- Student Scores Zone (Columns A, B, C): The starting input data with student names and raw exam scores. Keep these untouched.
- Grading Results Zone (Columns D & E): Where you calculate the average score and the Pass/Fail decision for each student.
- Class Summary (Row 8): The bottom row where you calculate overall class metrics (Class Average and Total Passed).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Student Name, Midterm, Final Exam |
Source Inputs |
Original Test Scores |
Starting exam scores. Formulas will use columns B and C as input. |
D2:D5 |
Average Score |
Calculation |
=AVERAGE(B2:C2) |
Calculate the average score between Midterm and Final for each student. |
E2:E5 |
Academic Standing |
Decision (IF) |
=IF(D2 >= 70, "PASS", "FAIL") |
Output "PASS" if score is 70 or higher, otherwise output "FAIL". |
B8 |
Summary: Class Average |
Overall Metric |
=AVERAGE(D2:D5) |
Find the average score of the entire class across all students. |
C8 |
Summary: Total Passed |
Count Metric |
=COUNTIF(E2:E5, "PASS") |
Count how many students earned a "PASS" grade. |
Each student is evaluated individually on their own row, and the bottom row gives the instructor a quick look at overall class performance.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate the Average Score of Midterm and Final using AVERAGE.
2
In Column E (E2:E5), assign "PASS" if Average >= 70, otherwise "FAIL" using IF.
3
In cell B8, calculate overall Class Average using AVERAGE.
4
In cell C8, count how many students received "PASS" using COUNTIF.
Solution & Formula Breakdown
Here is the simple, step-by-step explanation for each grading formula:
1. Calculating the Student Average (Column D)
The AVERAGE(range) function adds up all numbers in the selected cells and divides by how many numbers there are:
=AVERAGE(B2:C2)
Example for Alex Reed (Row 2):
- Midterm Score:
82
- Final Exam:
78
- Math:
(82 + 78) / 2 = 160 / 2 = 80
- The formula outputs
80 in cell D2.
2. Assigning Pass or Fail with IF (Column E)
The IF function lets Excel make a decision based on a simple condition:
=IF(D2 >= 70, "PASS", "FAIL")
How the IF function works:
D2 >= 70 is the test: "Is the average score in D2 greater than or equal to 70?"- If YES (True): Excel gives
"PASS".
- If NO (False): Excel gives
"FAIL".
Example: Jordan Lee has an average of 66.5. Since 66.5 is less than 70, Excel assigns "FAIL".
3. Overall Class Summary (Cells B8 & C8)
-
Cell B8 (Class Average):
=AVERAGE(D2:D5)
Calculates the average of all 4 student scores (80, 66.5, 93.5, 66) → resulting in an overall class mean of 76.5.
-
Cell C8 (Total Students Passed):
=COUNTIF(E2:E5, "PASS")
The COUNTIF function looks through the column of results and counts only the cells that say "PASS". In this class, exactly 2 students passed.
When typing text values inside Excel formulas (like "PASS" or "FAIL"), always wrap the words in double quotation marks ("...") so Excel knows they are words and not formula names!