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

By Ali, Founder, Excel Lessons

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

Project: Budget Against Actual Projects
Project: A Stock Reorder List Projects
COUNT and COUNTA Spreadsheet Basics
SUMIF Conditional Math

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

>= means at least: greater than or equal.

In `E2`, type:

=B2>=150

A screening is busy at 150 tickets or more.

Row	A	B	C	D
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

>= means at least: greater than or equal. In E2, type: =B2>=150. A screening is busy at 150 tickets or more.

Hint. B2, >=, 150.
2

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

Hint. Compare 205 with 150.
3

A screening is sold out when tickets sold equals capacity. In E4, ask whether Friday evening sold out. Compare tickets sold with capacity using a single = sign.

Hint. B4 equals C4.
4

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

Hint. B5 less than C5.
5

Text compares too, and <> means not equal. In E6, ask whether Saturday evening's certificate is not PG. The bar stays open late for anything that is not a PG. Remember: text goes in quotes, numbers do not.

Hint. D6 <> "PG".
6

Text comparisons ignore case. Read =D3="pg" and say what it returns. D3 holds PG in capitals.

Hint. Case does not count.