The Problem
Grade sheets often start as a simple list of scores, but they become harder to review when the list has to answer a bigger question: who actually passed? A teacher, trainer, or team lead can scan a few rows by hand, but once the class grows, manual checking becomes slow and easy to second-guess.
The problem is not only the calculation. The sheet also needs to make the decision consistent from row to row. If one student has two exam scores, the final status should come from the same rule every time: average the scores first, compare that result with the pass mark, then use a clear label that anyone can read quickly.
The flow below shows why the average and the label belong together. The raw scores are useful, but the worksheet becomes much more helpful once those scores turn into a repeatable decision and a quick class summary.
The Problem: Consistent Pass/Fail Decisions Scores need to become a repeatable status, not a manual judgment made one row at a time.
That is the setup for this workbook. Each student has a midterm score and a final score, the pass mark is stored separately, and the sheet needs both row-level results and a short audit summary. The user should be able to see the average, see the status, and then confirm how many students were processed without hunting through the list.
- Each student row needs one average score.
- Each average needs one clear
PASS or FAIL result.
- The summary should count the roster and the passing students.
That keeps the sheet useful for both detail work and quick review. The row formulas explain what happened to each student, while the summary gives a clean class-level answer.
How We Solve It
The logic is straightforward. First, calculate the average of the two exam scores in each row. Then compare that average with the pass mark and return either PASS or FAIL.
The challenge uses AVERAGE for the score calculation, IF for the final decision, and COUNTIF in the summary.
Method 1: Average First, Then Check with IF
Method 1: Compare the average score against a pass threshold with IF.
This two-step pattern is the best fit when the workbook needs to show its work. The average column gives the reviewer a number they can verify, and the status column turns that number into the final decision.
The key detail is keeping the decision formula pointed at the average, not at just one of the test scores. That avoids a common grading mistake where the final label is based on the wrong input.
=IF(D2>=70,"PASS","FAIL")
Method 2: Combine the Logic
Method 2: Calculate the average and the decision in one formula.
If the sheet only needs the final label, you can place the average calculation inside the decision formula. This keeps the worksheet compact because the user sees the result without a separate helper column.
The tradeoff is readability. A compact formula is fast, but it hides the intermediate average, so it is better for simple reports than for sheets where someone needs to audit the score behind the decision.
=IF(AVERAGE(B2:C2)>=70,"PASS","FAIL")
Method 3: Use IFS for More Grade Bands
Method 3: Expand the logic when you need more than a simple pass or fail result.
If the workbook later grows beyond a simple pass/fail label, IFS is a natural next step. It lets the formula check several score bands in order, so the same average can become a letter grade or a more detailed performance label.
The important part is the order of the thresholds. Higher score bands should be checked first, otherwise a strong score could match a lower band before Excel reaches the better label.
=IFS(D2>=90,"A",D2>=80,"B",D2>=70,"C",TRUE,"F")
Function Explanation
1. AVERAGE
AVERAGE turns the midterm and final score into one review number. That matters because the pass/fail decision should be based on the combined performance, not whichever score someone happens to notice first.
In the workbook, this also makes the sheet easier to audit. If a status looks surprising, the reviewer can look one cell to the left and see the score that drove the decision.
2. IF
IF is the decision point. It checks whether the average meets the pass mark and returns one label when the condition is true and another when it is false.
That is exactly why it fits this challenge: the worksheet needs a clear yes/no-style outcome, but the decision still has to come from a numeric rule.
Learn more this functionIF
3. IFS
IFS is useful when one threshold is no longer enough. Instead of nesting several IF formulas, you can list the score bands in a readable order.
Even though the challenge only needs pass and fail, this method shows the upgrade path if the grade sheet later needs labels like A, B, C, and F.
Learn more this functionIFS
If your pass mark changes later, using a dedicated threshold cell can make the workbook easier to maintain than hard-coding the number in every formula.
Calculate the average score for each student, then use that result to decide whether the student passes or fails the class. When the row logic is complete, finish the summary section so the worksheet clearly shows both the total number of students and how many passed.