Cellwise Try it free

Excel guide · 3 min read

Pass/fail formula in Excel

Quick answerUse =IF(B2>=50,"Pass","Fail"), replacing 50 with your pass mark. Better still, type the pass mark in its own cell and point to it with a locked reference, =IF(B2>=$F$2,"Pass","Fail"), so changing one cell re-marks the whole class.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEF
1StudentMathEnglishSciencePass mark
2Aisha Khan72586450
3Ben Ortiz456170
4Chloe Nguyen889179
5Daniel Reyes504955
6Emma Walsh3962
7Farid Haddad677448

The basic pass/fail formula

IF asks one question: is the score at least the pass mark? If yes it returns the second argument, if no the third. Aisha scored 72 in Math, so she passes.

Use >= (greater than or equal), not >. With > a student who scores exactly 50 fails, which is the most common complaint about pass/fail sheets. The $ signs in $F$2 keep the formula pointing at the pass mark when you fill it down and across.

=IF(B2>=$F$2,"Pass","Fail")

Result: Pass

72 is at least 50, so the result is Pass.

Pass only if every subject is passed

Many schools and certification exams require a pass in each part. Wrap the tests in AND, which is TRUE only when all of them are TRUE. Ben has 61 and 70 but 45 in Math, so he fails overall.

With many columns, AND gets long. Count the failures instead: COUNTIF counts the scores below the pass mark, and zero failures means a pass. This version keeps working when you add a subject, as long as the range covers it.

=IF(COUNTIF(B3:D3,"<"&$F$2)=0,"Pass","Fail")

Result: Fail

One score (45) is below 50, so the count is 1 and Ben gets Fail.

Pass on the average, with a minimum per subject

Another common rule: the average must reach the pass mark and no single subject may fall below a floor, say 45. Daniel averages 51.3 but has 49 in English. Under a strict every-subject rule he fails; under this rule he passes.

Pick the rule your institution actually uses and write it down next to the sheet. Most disputes come from a formula that quietly applies a different rule from the handbook.

=IF(AND(AVERAGE(B5:D5)>=50,MIN(B5:D5)>=45),"Pass","Fail")

Result: Pass

The average is 51.3 and the lowest score is 49, both above their limits, so Daniel passes.

Absent or missing scores

Emma has no English score. Excel treats a blank cell as 0 in a comparison, so =IF(C6>=$F$2,"Pass","Fail") marks her Fail as if she had sat the exam and scored nothing. That is rarely what you want.

Check for missing scores first. COUNT counts only cells that contain numbers, so fewer than 3 means something is missing.

=IF(COUNT(B6:D6)<3,"Incomplete",IF(MIN(B6:D6)>=$F$2,"Pass","Fail"))

Result: Incomplete

Only two of three scores are present, so the result is Incomplete instead of a misleading Fail.

Distinction, Merit, Pass or Fail

For more than two outcomes, test from the highest band down. IFS (Excel 2019 and later, including Microsoft 365) takes pairs of test and result; TRUE at the end catches everything else. In older Excel, nest IF: =IF(B4>=85,"Distinction",IF(B4>=65,"Merit",IF(B4>=50,"Pass","Fail"))).

Order matters. If you test B4>=50 first, every distinction student stops at Pass.

=IFS(B4>=85,"Distinction",B4>=65,"Merit",B4>=50,"Pass",TRUE,"Fail")

Result: Distinction

Chloe scored 88 in Math, which meets the first test, so she gets Distinction.

Count passes and the pass rate

COUNTIF with ">="&F2 counts every score at or above the pass mark. Note the quotes around the operator and the & joining it to the cell; ">=F2" in one string would compare against the text F2 and count nothing. Divide by COUNT to get the pass rate.

To color the results, select the Pass/Fail column and use Home > Conditional Formatting > Highlight Cells Rules > Equal To, type Fail and pick a red fill.

=COUNTIF(B2:B7,">="&F2)/COUNT(B2:B7)

Result: 0.66666666667 shows as 67% with Percent format

4 of the 6 Math scores are 50 or more, a pass rate of about 67%.

Common mistakes

Using > instead of >=

A score exactly on the pass mark should usually pass. Write B2>=$F$2.

Typing the pass mark into every formula

Put it in one cell (F2) and reference it with $F$2. Changing the mark then takes one edit instead of hundreds.

Blank scores marked as Fail

Test COUNT or ISBLANK first and return Incomplete or Absent, so missing work is not confused with a zero.

Scores stored as text

Imported marks that sit on the left of the cell may be text, and text compares as larger than any number, so "45" passes. Convert them with the warning icon > Convert to Number.

FAQ

How do I write a pass or fail formula in Excel?

=IF(B2>=50,"Pass","Fail") returns Pass for 50 and above and Fail below. Replace 50 with a reference to a pass-mark cell such as $F$2.

How do I mark pass if all subjects are above 40?

Use =IF(AND(B2>=40,C2>=40,D2>=40),"Pass","Fail"), or for many subjects =IF(COUNTIF(B2:H2,"<40")=0,"Pass","Fail").

How do I calculate the pass percentage in Excel?

Divide the number of passes by the number of scores: =COUNTIF(B2:B30,">=50")/COUNT(B2:B30), then format the cell as a percentage.

Can the formula return a blank until a score is entered?

Yes. Wrap it: =IF(B2="","",IF(B2>=$F$2,"Pass","Fail")). The cell stays empty until a score is typed.

Functions used

Related guides

Updated