IF and logical tests

2 min read Quiz included

IF checks a condition and returns one value if it's true and another if it's false:

IF tests one condition and returns one of two values. AND, OR and NOT build bigger tests to put inside it.

Excel formula

=IF(condition, value_if_true, value_if_false)

	A	B	C
1	Student	Score	Result
2	Asha	82	`=IF(B2>=50, "Pass", "Fail")` → Pass
3	Leo	41	`=IF(B3>=50, "Pass", "Fail")` → Fail

Text results go in double quotes; numbers don't.

Calculations in IF

Excel formula

=IF(B2>1000, B2*10%, 0)          → a 10% bonus for sales over 1000
=IF(C2="", "Missing", C2*2)      → handle an empty cell

AND, OR, NOT

Excel formula

=IF(AND(B2>=50, C2>=75%), "Pass", "Fail")      → both must be true
=IF(OR(D2="VIP", E2>5000), "Free shipping", "Standard")
=IF(NOT(F2="Cancelled"), "Active", "Closed")

Nested IFs

For more than two outcomes, put an IF inside another:

Excel formula

=IF(B2>=90, "A", IF(B2>=75, "B", IF(B2>=50, "C", "F")))

Excel tests from the outside in, so check the highest band first.

IFS: cleaner multiple conditions

Excel 2019 and later (and Microsoft 365) have IFS, which lists condition/result pairs in order:

Excel formula

=IFS(B2>=90, "A", B2>=75, "B", B2>=50, "C", TRUE, "F")

The final TRUE acts as "everything else".

IFERROR: friendly error handling

Excel formula

=IFERROR(B2/C2, 0)                              → 0 instead of #DIV/0!
=IFERROR(VLOOKUP(A2, Prices!A:B, 2, FALSE), "Not found")

Finished this lesson?Mark it complete to earn +20 XP and move your progress on.