IF returns one result when a condition is true and another when it is false. Check for an empty score first so that an ungraded entry is not marked as a failure.
Visual walkthrough
Before
The scores are 85, 70, 69 and a blank cell.
After
The results are Pass, Pass, Fail and Not graded.
Check the boundary score 70 and the blank score.
Try it here
Change the selection to see the result and matching data rows.
=IF(B2="","Not graded",IF(B2>=70,"Pass","Fail"))| Score | Result |
|---|---|
| 85 | Pass |
| 70 | Pass |
| 69 | Fail |
| Blank | Not graded |
Sample data
Download sample CSV| Name (A) | Score (B) |
|---|---|
| Alex | 85 |
| Blake | 70 |
| Casey | 69 |
| Drew |
If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.
Step-by-step instructions
- Enter the sample names and scores in columns A and B.
- Enter the formula below in C2.
=IF(B2="","Not graded",IF(B2>=70,"Pass","Fail")) - Copy the formula down. Scores 85 and 70 should show Pass, while 69 should show Fail.
- Leave the last score empty and check that its result is Not graded.
Frequently asked questions
Can I change the pass mark?
Yes. Replace 70 in the formula, or put the threshold in a cell such as E1 and use B2>=$E$1.
Reference: Microsoft Support