TRUE, FALSE and the Question Behind Them

About this lesson

A comparison such as B2>B8 is a formula that returns TRUE or FALSE. Every IF, COUNTIF and filter in Excel is built on one.

By the end you can

write a comparison that returns TRUE or FALSE.
compare text as well as numbers, and add up a column of flags.
say whether a value on the line counts as above it.

Practises IF AND OR

By Ali, Founder, Excel Lessons

The idea

Before IF, there is the question IF asks. A comparison on its own is a complete formula: an equals sign, one cell, a sign, another cell. It returns TRUE or FALSE with nothing around it. A column of them is a column of flags. TRUE is 1 and FALSE is 0 underneath, so adding the column counts the flags.

The edge is the thing to be exact about. Greater-than does not include equal. So a reading exactly on a limit is not above it. Most arguments about a report's flags are arguments about which sign the rule meant.

The mistake to watch for

Treating at-or-above as above. A comparison is exact about its edge. A reading equal to the maximum is not above it. So a rule that means at or above needs >=. A rule written with > lets the boundary case through. Decide what the boundary means before you pick the sign.

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

A comparison asks a yes-or-no question and answers TRUE or FALSE.

In `D2`, type:

=B2>B8

B8 is the safe maximum for this fridge.

Row	A	B	C
1	Date	Temp C	Checked by
2	Mon 6 Jan	4.2	R Okafor
3	Tue 7 Jan	5.1	R Okafor
4	Wed 8 Jan	8.6	L Hastie
5	Thu 9 Jan	3.9	L Hastie
6	Fri 10 Jan	7.4	R Okafor
7
8	Safe maximum	8

Every step

1

A comparison asks a yes-or-no question and answers TRUE or FALSE. In D2, type: =B2>B8. B8 is the safe maximum for this fridge.

Hint. B2, greater than, B8.
2

Read =B4>B8 and say what it returns. Wednesday's reading is 8.6.

Hint. Compare 8.6 with 8.
3

In D5, ask the same question about Thursday.

Hint. B5 against B8.
4

TRUE and FALSE are numbers underneath: TRUE is 1 and FALSE is 0. In D6, add D2 and D4 with a plus sign. See what number comes out.

Hint. D2 plus D4.
5

Comparisons work on text too. A single = sign checks whether two things match. In D8, ask whether Monday and Tuesday were checked by the same person.

Hint. C2 equals C3.
6

Suppose Tuesday's reading had been exactly 8, the maximum itself. Read =B3>B8 with B3 at 8 and say what it returns.

Hint. 8 is not more than 8.