Excel track · Exercise 23 of 86 Paid

TRUE and FALSE: what a comparison returns

Read a comparison as a logical value that other formulas can act on.

Intermediate

Stock list with a total row to compare against

Read-only data · screen 1 of 2 · answer in B9

In B9, answer TRUE when the total units are over 100.

	A	B
1	Item	Units
2	Bolt	18
3	Nut	22
4	Washer	25
5	Screw	30
6	Clip	24
7	Pin	31
8	Total	150
9	Over 100?

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B9, answer TRUE when the average units are at least 20.

	A	B
1	Item	Units
2	Bolt	18
3	Nut	22
4	Washer	25
5	Screw	30
6	Clip	24
7	Pin	31
8	Total	150
9	Average at least 20?

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

A comparison is a formula in its own right. Enter =SUM(B2:B7)>100 and the cell holds TRUE, a logical value that IF, AND and conditional formatting all read directly. Wrap the word in quotes and the cell holds four characters of text, which sorts with labels and fails a logical test.

The six items add to 150, so the first screen answers TRUE. The second screen asks about the average, which is 25 units and clears a floor of 20. Point the operator the other way and the same numbers answer FALSE.

An IF wrapped around a comparison only passes along a value that was already worked out. Put the comparison in quotes instead, as in =IF(SUM(B2:B7)>100,"TRUE","FALSE"), and the cell returns two pieces of text that nothing else can test.

Syntax

=SUM(B2:B7)>100 returns TRUE or FALSE; it is the comparison, not a word

Worked example

On Stock list with a total row to compare against, =SUM(B2:B7)>100 returns true.

=SUM(B2:B7)>100

Common errors

Error codes for comparison operators returning logical values, and why quoted TRUE is not one
Error	What it means and how to fix it
`Quoted TRUE`	The cell holds the four characters TRUE as text, so a later IF reads a label where it expected a logical value.
`#NAME?`	A function name or a cell reference was mistyped, so Excel cannot tell which cells to compare.
`#DIV/0!`	The comparison is divided by zero, so the cell shows an error where TRUE or FALSE belonged.

At work

Flagging a month as over budget, then letting the next formula act on the flag.