What IF does
The IF function is the foundation for almost all conditional evaluations in Excel. It checks a condition and returns a different value depending on the result. Syntax:
=IF(condition, value_if_true, value_if_false)
Simple example: pass or fail
A student has a score in column B. Column C should show whether they passed (50 points or more):
=IF(B2>=50, "Pass", "Fail")
Result: if B2 is 72, the cell shows Pass. If B2 is 38, it shows Fail.
Nested IF: multiple cases
To distinguish more than two cases, nest IF formulas. Example: grade assignment by score:
=IF(B2>=90, "A", IF(B2>=75, "B", IF(B2>=60, "C", IF(B2>=45, "D", "Fail"))))
Excel evaluates conditions from left to right and returns the value for the first matching case.
IFS as an alternative
From Excel 2019, the IFS function makes nested IF formulas more readable:
=IFS(B2>=90, "A", B2>=75, "B", B2>=60, "C", B2>=45, "D", TRUE, "Fail")
The final entry TRUE, "Fail" acts as a default for all cases not covered above.
Practical use cases in school
- Attendance status:
=IF(C2="x", "Present", "Absent") - Flag students for support:
=IF(B2<40, "Review needed", "")– returns text only when below threshold, otherwise an empty cell - Traffic-light status for classes: combine with conditional formatting for visual overviews
Common mistakes
- Text values without quotes:
=IF(B2=pass, ...)does not work – text must be in quotes:"Pass" - Too many nesting levels: More than three or four levels become hard to read. Use IFS or a helper column with intermediate values instead.
- Missing brackets: Every opening bracket needs a closing one. Excel highlights missing brackets when you complete the formula.