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

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 takes several tests and returns TRUE only when every one of them is TRUE. An apprenticeship needs the applicant to be old enough and to have enough hours.

In `E2`, type `=AND(B2>=B7,D2>=B8)`.

Is Sana at least the minimum age, and does she have at least the minimum 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 takes several tests and returns TRUE only when every one of them is TRUE. An apprenticeship needs the applicant to be old enough and to have enough hours. In E2, type =AND(B2>=B7,D2>=B8). Is Sana at least the minimum age, and does she have at least the minimum 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 of its tests is TRUE. There is a second way in: a portfolio, or enough hours. In F4, ask whether Ruth gets in that way. Is her portfolio Yes, or are her hours at least the minimum?

Hint. Either test will do.
4

NOT flips a TRUE to FALSE and a FALSE to TRUE. The under-18s need a guardian's signature. In F3, put NOT around the age test to flag whether Owen is under the minimum age.

Hint. NOT around the age test.
5

In F2, write a formula using IF and AND. Show Interview if Sana meets the age minimum, meets the hours minimum and has a portfolio, all three. Show Reject otherwise.

Hint. IF, with AND as its test.