AND, OR and NOT

About this lesson

AND returns TRUE only when every test inside it is TRUE. OR returns TRUE when any one is. NOT reverses a test. So a rule with several conditions can sit inside a single IF.

By the end you can

combine conditions with AND and OR inside an IF.
flip a test with NOT and say when it is clearer than rewriting it.

Practises AND OR NOT IF

By Ali, Founder, Excel Lessons

The idea

Three small functions that turn several questions into one. AND is strict: every test must pass. OR is relaxed: any test will do. NOT reverses whatever it is given. Each returns TRUE or FALSE, so each can be the test of an IF. IF(AND(…), …) is how most real rules are written.

The mistake is writing a rule in words as "age over 18 or 21" and typing it as B2>18 OR 21. Every test inside AND or OR is a complete comparison of its own. It has its own cell and its own sign.

The mistake to watch for

Writing AND where the rule says either. AND is TRUE only when every test passes. OR is TRUE when any one does. A rule with two ways in needs OR. A rule that requires both needs AND. Say the rule out loud with the word and or the word or before you type the function.

Where this comes up again

Putting It Together Conditional Math
ISNUMBER, ISTEXT, ISBLANK and ISERROR Errors and Robust Formulas
Guarding a Division: IF or IFERROR? Errors and Robust Formulas
Errors: Putting It Together Errors and Robust Formulas

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

AND returns TRUE only when every test inside it is TRUE.

In `E2`, type:

=AND(B2>=B7,D2>=B8)

An apprenticeship needs enough age and enough hours.

Row	A	B	C	D
1	Applicant	Age	Portfolio	Hours worked
2	Sana K	19	Yes	220
3	Owen T	17	Yes	340
4	Ruth M	24	No	410
5	Femi A	21	Yes	95
6
7	Minimum age	18
8	Minimum hours	200

Every step

1

AND returns TRUE only when every test inside it is TRUE. In E2, type: =AND(B2>=B7,D2>=B8). An apprenticeship needs enough age and enough hours.

Hint. Two tests, one comma.
2

Read =AND(B5>=B7,D5>=B8) and say what it returns. Femi is 21 with 95 hours.

Hint. Both must pass.
3

OR returns TRUE when any one test is TRUE. In F4, ask whether Ruth's portfolio or hours qualify her. Shape: =OR(test, test). A portfolio counts as a second way in.

Hint. Either test will do.
4

NOT flips TRUE to FALSE and FALSE to TRUE. In F3, wrap the age test in NOT for Owen. Shape: =NOT(test). Anyone under 18 needs a guardian's signature.

Hint. NOT around the age test.
5

In F2, combine IF and AND for Sana's result. Shape: =IF(AND(test, test, test), if_true, if_false). Show Interview if all three pass, Reject otherwise.

Hint. IF, with AND as its test.