Excel Practice
Lessons Lesson 24

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

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

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 is a formula that asks a yes-or-no question. It answers TRUE or FALSE. This is a vaccine fridge log. Any reading above the safe maximum in B8 has to be reported.

In D2, type =B2>B8: is Monday's reading greater than the safe maximum?

D2
Row A B C D E F G
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. 1

    A comparison is a formula that asks a yes-or-no question. It answers TRUE or FALSE. This is a vaccine fridge log. Any reading above the safe maximum in B8 has to be reported. In D2, type =B2>B8: is Monday's reading greater than the safe maximum?

    Hint. B2, greater than, B8.

  2. 2

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

    Hint. 8.6 against 8.

  3. 3

    In D5, ask the same question about Thursday.

    Hint. B5 against B8.

  4. 4

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

    Hint. D2 plus D4.

  5. 5

    Comparisons work on words too, and a single = sign asks whether two things are the same. In D8, ask whether Monday and Tuesday were checked by the same person.

    Hint. C2 equals C3.

  6. 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.