Excel Practice
Lessons Lesson 25

The Six Comparisons

About this lesson

Excel has six comparison operators: greater than, less than, equal, not equal, at least and at most. Each returns TRUE or FALSE for any two values, numbers or text.

By the end you can

  • use all six comparisons, including not-equal and at-least.
  • compare text as well as numbers and say what Excel does with case.

Practises IF COUNTIF EXACT

The idea

Six signs. Two of them are typed as pairs: >= for at least and <= for at most, with the arrow first. And <> for not equal. Every rule on a sheet is one of the six. Most wrong flags come from choosing between > and >=. The rule said "over 150" and the formula said "150 or more".

Text compares with the same signs. The thing to know is that Excel ignores case. So PG and pg are equal. That is what you want for a certificate and not what you want for a password.

The mistake to watch for

Expecting a text comparison to notice capitals. = and <> ignore case. So pg and PG are equal, and a test written to catch one catches both. Usually that helps. When case carries meaning, EXACT is the function that respects it.

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

There are six comparisons: > greater than, < less than, = equal, <> not equal, >= at least, <= at most. The cinema counts a screening as busy if it sold at least 150 tickets.

In E2, type =B2>=150: did Thursday sell 150 or more?

E2
Row A B C D E F G
1 Screening Tickets sold Capacity Certificate
2 Thu 19:30 148 180 12A
3 Fri 17:00 96 180 PG
4 Fri 20:15 180 180 15
5 Sat 14:00 205 240 PG
6 Sat 20:15 240 240 15
7
8

Every step

  1. 1

    There are six comparisons: > greater than, < less than, = equal, <> not equal, >= at least, <= at most. The cinema counts a screening as busy if it sold at least 150 tickets. In E2, type =B2>=150: did Thursday sell 150 or more?

    Hint. B2, >=, 150.

  2. 2

    Read =B5>=150 and say what it returns. Saturday's matinee sold 205.

    Hint. 205 against 150.

  3. 3

    A screening is sold out when tickets sold equals capacity. In E4, ask whether Friday evening sold out, comparing the two cells with a single = sign.

    Hint. B4 equals C4.

  4. 4

    In E5, ask whether Saturday's matinee still had seats: fewer tickets sold than the room holds.

    Hint. B5 less than C5.

  5. 5

    Text compares too, and <> means not equal. The bar stays open late for anything that is not a PG. In E6, ask whether Saturday evening's certificate is something other than PG, with the text in quotation marks.

    Hint. D6 <> "PG".

  6. 6

    One thing about text. Read =D3="pg", the certificate typed in lower case, and say what it returns.

    Hint. Case does not count.