Conditional formulas: IF, AND, OR

In this lesson you will learn

Write the three arguments of IF correctly
Check several thresholds with nested IFs
Build compound conditions with AND, OR and NOT

The pass mark in a test is 50. You can check 5 students' results by eye — but what about 300 students? Excel can decide for each row by itself: “if the score is 50 or more — pass, otherwise — fail”. That is what the IF function is for.

The IF function

=IF(logical_test, value_if_true, value_if_false)

where:

logical_testthe condition to check, e.g. B2>=50; its result is TRUE or FALSE
value_if_truethe value returned when the condition is met
value_if_falsethe value returned when it is not

Excel

=IF(B2>=50,"Pass","Fail")
=IF(C2>=100,C2-10,C2)
=IF(B2="","",B2*2)

1) “Pass” if the score is 50 or more, otherwise “Fail”. 2) 10 ₼ off purchases of 100 ₼ or more. 3) If B2 is empty, leave the cell empty too.

IF can return not only text but numbers and even calculations. For example, an online shop delivers orders of 50 ₼ or more for free and charges 3 ₼ for the rest: =IF(C2>=50,0,3). The total to pay is then =C2+IF(C2>=50,0,3) — IF works inside the formula just like a number.

Nested IF: a grading scale

From score to mark

A school uses this scale: 90 and above — 5, 75–89 — 4, 50–74 — 3, below 50 — 2. Which formula gives the mark for the score in B2?

Show solution

Check the thresholds from highest to lowest; each next IF goes into the “otherwise” part of the previous one:
=IF(B2>=90,5,IF(B2>=75,4,IF(B2>=50,3,2)))
If B2 = 80: 80 ≥ 90 is false → next IF; 80 ≥ 75 → result 4.
In Microsoft 365, IFS does the same job: =IFS(B2>=90,5,B2>=75,4,B2>=50,3,TRUE,2).

AND, OR and NOT

Function	Returns TRUE when…	Example
AND	all conditions are true	`=AND(B2>=80,C2>=80)`
OR	at least one condition is true	`=OR(B2<50,C2<50)`
NOT	the condition is false (it flips the result)	`=NOT(B2>=50)`

On their own these functions return only TRUE or FALSE, so they are usually placed inside IF. For example, a grant goes to students with 80 or more in both subjects: =IF(AND(B2>=80,C2>=80),"Yes","No"). Writing =50<=B2<=100 as in maths does not work — use =AND(B2>=50,B2<=100) instead.

Interactive

Loading simulation…

IF, AND and a nested IF in one table: pass/fail, grant and mark.

Key points

=IF(test, if_true, if_false) picks one of two results based on a condition.
Text goes in straight quotes; text without quotes gives #NAME?.
In nested IFs, check thresholds from highest to lowest; Microsoft 365 also has IFS.
AND — all conditions, OR — at least one, NOT — the opposite.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

If B2 = 50, what does =IF(B2>=50,"Pass","Fail") return?