The Problem
In this guide, we will explore exactly how to calculate pass/fail grades in Excel. This kind of task shows up anywhere scores have to be turned into a clear decision.
The row values alone are not enough because the pass rule depends on the average of two scores, not just one cell. If that average is checked manually, mistakes can slip in quickly once the list gets longer.
In this challenge, each student has a midterm score and a final score. You need to calculate the average, decide whether that average passes the threshold, and then summarize how many students passed overall.
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 easy to read. One column calculates the average, and the next column turns that result into a pass or fail label.
That layout is helpful when you want the worksheet to show both the number and the decision side by side.
=IF(D2>=70,"PASS","FAIL")
Method 2: Combine the Logic
Method 2: Calculate the average and the decision in one formula.
If you do not need a separate average column, you can nest AVERAGE inside IF. That keeps the logic compact.
This method is useful when the worksheet should show only the final status, not the intermediate average.
=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 needs letter grades or multiple score bands, IFS is a natural next step. It checks several thresholds in order and returns the first match.
That is beyond the exact requirement here, but it is a useful extension of the same grading idea.
=IFS(D2>=90,"A",D2>=80,"B",D2>=70,"C",TRUE,"F")
Function Explanation
1. AVERAGE
AVERAGE adds the scores and divides by the count. In this challenge, it creates the class average used for the final decision.
That makes it the first step in the whole grading flow, because the pass or fail label depends on this result.
2. IF
IF tests a condition and returns one result when it is true and another when it is false. Here, it checks whether the average is at least 70.
That makes it the decision point that turns a number into a final status.
Learn more this functionIF
3. IFS
IFS is useful when one threshold is no longer enough. It lets the formula move through multiple grading bands without nesting several IF statements together.
Even if the current challenge only needs pass and fail, it shows where the logic can go next.
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.