Pass/Fail Formula in Excel With IF

A pass fail formula in Excel checks each score against a pass mark and shows a label: =IF(B2>=50,"Pass","Fail"). It is one of the first IF formulas most people write, and it turns up in gradebooks, training records and job tests. This guide shows the basic formula, how to keep the pass mark in a cell, how to handle two subjects or three results, and how to count the passes.

How to write a pass/fail formula in Excel#

The pass/fail formula is an IF with a comparison inside it. IF checks a condition and returns one value when the condition is TRUE and another when it is FALSE:

=IF(logical_test, value_if_true, value_if_false)

All the examples use this table of test scores in A1:B8, with a pass mark of 50:

Follow these steps:

1. Click C2.
2. Type =IF(B2>=50,"Pass","Fail").
3. Press Enter. Aisha scored 72, so C2 shows Pass.
4. Select C2:C8 and fill down (Windows: Ctrl+D; Mac: ⌘D). You can also double-click the small square at the bottom-right of C2.

Ben (45), Dev (38) and Grace (49) get Fail. Everyone else gets Pass. The words Pass and Fail need double quotes because they are text. If IF itself is new to you, start with the Excel IF function explained simply.

Should you use > or >= for the pass mark?#

Use >= (greater than or equal to) when a score equal to the pass mark is a pass, which is the usual rule. Chloe scored exactly 50:

That one character changes the result for anyone sitting right on the line, so check the rule before you write the formula. In Excel, "greater than or equal to" is written >=. The order => is an error.

How to keep the pass mark in a cell#

Put the pass mark in its own cell so you can change it without editing every formula. Type Pass mark in E1 and 50 in F1. Then write this in C2 and fill it down:

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

The $ signs make F1 an absolute reference: it stays pointing at F1 as the formula moves down the rows, while B2 changes to B3, B4 and so on. To add the dollar signs, click on F1 in the formula bar and press F4 on Windows or ⌘T on a Mac.

Now change F1 to 60 and the whole column updates. Farid (64) still passes, but Chloe (50) now fails. This is also how a job test often checks you: it changes an input and expects your results to follow.

How to make a pass/fail formula with two conditions#

Use AND inside IF when a student must pass every part. Say column B is the theory score and column C is the practical score. A student passes only if both are 50 or more:

=IF(AND(B2>=50,C2>=50),"Pass","Fail")

AND returns TRUE only when every test inside it is TRUE. If passing either part is enough, swap AND for OR. To pass on the average instead, test the average:

=IF(AVERAGE(B2:C2)>=50,"Pass","Fail")

These three rules can give different results for the same student, so pick the one your course or employer actually uses. The guide to IF with AND for several conditions has more examples, and IF between two numbers shows how to test a score inside a range, such as 40 to 49 for a resit.

How to add a third result like Distinction#

For three or more results, use IFS or a nested IF and test the highest band first. Here 85 or more is a Distinction, 50 or more is a Pass and anything lower is a Fail:

=IFS(B2>=85,"Distinction",B2>=50,"Pass",TRUE,"Fail")

IFS checks the pairs from left to right and stops at the first TRUE test. The final TRUE,"Fail" pair catches every score that is left. Elena (91) gets Distinction, Aisha (72) gets Pass and Ben (45) gets Fail.

IFS needs Excel 2019, Excel 2021 or Microsoft 365. In older versions, a nested IF does the same job:

=IF(B2>=85,"Distinction",IF(B2>=50,"Pass","Fail"))

Order matters in both. If you test B2>=50 first, Elena's 91 stops there and never reaches Distinction. The guide to nested IF for grades and bands explains why.

How to handle blank scores#

An empty cell counts as 0 in a comparison, so a student who hasn't taken the test yet shows Fail. Check for a blank first and return empty text:

=IF(B2="","",IF(B2>=50,"Pass","Fail"))

"" means empty text. The cell looks blank until a score is typed in. You can also return a word such as "Absent" instead of "". Be careful with text in the score column, too: a score typed as text, like "72 " with a space, can give the wrong result. The article on IF with text covers how IF compares words.

How to count passes and work out the pass rate#

Once column C shows Pass and Fail, COUNTIF counts each label:

Format the pass rate cell as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%) to see 57%. You can also skip the helper column and count scores directly: =COUNTIF(B2:B8,">=50") also returns 4.

Practise it#

The pass/fail formula is short, but the details (quotes, >=, the locked pass mark) are exactly what a timed test checks. Practise the IF function in a live spreadsheet: you type the formula and it checks it instantly. Then move on to nested IF and IFS for three or more results, and keep the IF function guide open as a quick reference. When you feel ready, try the basic Excel test under time pressure.