IF Statements and Logical Operators

Back in the original Star Trek days, my grandfather made fun of “Dr. Spock’s” funny ears—conflating the Enterprise science officer with the baby-care expert who came to fame a decade before. I loved Mr. Spock for his adherence to logic above all else (and his cocked-eyebrow reaction to Captain Kirk’s lack of same). It’s probably not coincidental that when I first encountered computer programming, I was most enamored of its use of logical functions, and that affection carried over to their use in spreadsheets.

But, enough history! This chapter starts by delving into the concept of truth, as interpreted by a spreadsheet formula, with a quick side trip about Comparison Operators. Then (with apologies to my vegan son) we get to the meat of the matter: constructing IF Statements. You’ll learn how Logical Operators (AND, OR, and NOT) expand the usefulness of IF statements, and then you’ll be Putting It All Together to construct a formula. Finally, I’ll introduce you to something more advanced: the incredibly convenient and flexible Nested IF Statements.

What Is Truth?

What is truth? Surely, a question for the ages. Except in a spreadsheet, where the definitions for true and false are straightforward:

• Numbers and expressions that evaluate to numbers: A zero is false, and all other numbers are true. So, 42 is true; so are 3÷5, and -15, and a reference to cell B7 if it contains a non-zero number.
• Numeric comparisons: Comparisons are intrinsically true or false: 4<3 and 5=7 are false, while 12>10 and 4×3>10 are true.
• Text: While text by itself is “false” (since it’s not a non-zero number), it can be used in the truth test of an IF statement. So, if the name Bartleby is in B2, the comparison in the statement IF B2="Bartleby" is considered true.
• Blank cells: References to blank cells return a false result.

The importance of “truth” in a spreadsheet is not an issue of accuracy, but one of what will be entered in a cell based on some condition that you can test—the equivalent of If HighestSalesFigure is greater than $5000, then put “High Sales Award” in this cell.

Comparison Operators

While I shouldn’t assume anything at all about you, I wrote 4>3 in the previous topic (just above), assuming that you know it means “4 is greater than 3,” and that 4<3 means “4 is less than 3.” Just as plus, minus, and equals signs are considered mathematical operators, the greater than and less than symbols are comparison operators, and they get heavy-duty use in IF statements.

The other, slightly less common, comparison operators are ≥ (greater than or equal to), ≤ (less than or equal to), and ≠ (does not equal). While you could enter those characters by choosing Edit > Emoji & Symbols, you don’t have to: you can just type the two-character version of an operator (>=, <=, or <>), and Numbers replaces it with the single-character version.

IF Statements

The IF function has a very simple job: judge whether an expression is true or false, and enter something into a cell based on that evaluation. Its syntax is simple: IF(test, if-true, if-false), which translates into simply: If this thing is true, then enter this, otherwise enter that. So, IF(A1 ≥ 65,"Pass","Fail") would put Pass into the formula cell if the grade in A1 is 65 or better.

If a customer gets a 10% discount when her order is $100 or over, you can use a single IF statement to calculate the overall total. With the subtotal in C10, the formula would be (as typed, and with Numbers’ symbol substitution):

IF(C10 >= 100, C10 * .9, C10) | IF(C10 ≥ 100, C10 × .9, C10)

This would give a 10% discount to orders of $100 and over by returning 90% of the order amount (C10 * .9) as the total; if it’s under $100, the unaltered amount in C10 is used as the total.

But you’d more likely do this particular calculation in two steps to make the situation clear at a glance (and to extract information about all the discounts you’ve given). Figure 106 shows the formulas used to calculate an allowed discount, and then the total based on that discount. (It also shows the use of header titles instead of cell coordinates in a formula, as described in Referencing by Header Names.)

You don’t have to use a comparison as the IF test; all you need is something that can be evaluated as true or false according to the definitions of those values. So, to test if the value in cell K9 is a non-zero number, you can simply use:

IF(K9,result-for-non-zero,result-for-zero)

This is a construct you’ll use often if you use checkbox input controls, described in Checkboxes. A checkbox cell stores TRUE for a checked box and FALSE for an unchecked one. If C3 is formatted for a checkbox, you don’t have to start with IF(C3=TRUE…. Since the cell resolves to being true if it’s checked, you can start with the simpler IF(C3…. If you do use TRUE or FALSE in a test, however, don’t put it in quotes as you do with normal text; when you type either (unquoted) word in the editor, it’s turned into a token.

Logical Operators

You’re probably familiar with logical operators even if you didn’t know that’s what they’re called, because you can use them in the Finder and with Google or other search engines. They let you specify, when you use more than one search term, whether all the terms, or any one of them, must be present for the search to be satisfied; they also let you define whether the presence of one of the terms disqualifies a document or Web site from being included (so, you can search in the Finder with hermit NOT crab to find documents about beings who live alone in caves, and not those who live in cast-off gastropod shells).

Using one of the three logical functions (AND, OR, NOT) with values always boils down to either a true or false result:

• AND: All of the provided expressions must be true for the final result to be true. 5>3 AND 2+2=4 AND 4 is true (the last item is true because a non-zero number is true). 3 AND 6=5 AND 2<9 is false because the second expression is false. In a formula, cell references will replace most of the numbers, so the results for B9>100 AND E7=C2 depend on the contents of those cells.
• OR: Only one of the provided expressions must be true for the final result to be true. 5>3 OR 3=7 OR 5=2+4 is true because of the first comparison.
• NOT: If any of the provided expressions is false, the result is false. NOT is also used to reverse the true/false status of an expression: if D4 is true, NOT(D4) is false, and vice versa.

The syntax for using a logical operator is rather fractured from the English point of view, but it’s designed to make it easier to enter and read in a formula. Instead of writing these two expressions like this:

B5>3 AND C10=7 AND A2+B2>K4 | B5>3 OR C10=7

you skip the repetitive operators and list the “tests” in parentheses:

AND (B5>3, C10=7, A2+B2>K4) | OR (B5>3, C10=7)

NOT has the same syntax as the other logical operators, and as a result is very different from its English syntax, which uses a combination of “not/or” or “neither/nor” to say “Not Bartleby or Scrivener.” But it’s simple and clear when the negated items are listed in parentheses:

NOT ("Bartelby","Scrivener")

Putting It All Together

Now that you know all the pieces—comparison operators, the IF statement with its test and dependent results, and the construct of logical operators and their arguments—you can combine them to build extremely useful formulas.

Say you want to calculate a discount for a customer who orders either a total of over $100 worth of merchandise or any one item that’s worth over $40. In English, it’s: If the total order is over $100, or if any one item is over $40….

With the order subtotal (pre-discount) in B5, and the highest-priced item in C2, that translates to this mathematical test:

OR (B5>100, C2>40)

As long as at least one of these expressions is true, we want to calculate the discount, which is 10% of the subtotal in B5. If neither is true, we want a zero in place of the discount amount.

Here’s the IF function syntax and the formula used for the items total cell shown in Figure 107:

IF(test,true-result,false-result)

IF(OR (B5>100,C2>40), B5 × .1, 0)

Nested IF Statements

An IF statement is great when you have two, and only two, possibilities that you must take into account: if the thing you test for is true, the function returns the primary result, otherwise it returns the alternative result. But when you need to allow for three or four results, nested IFs come to the rescue.

Back in Sort Unsortables, I talked about situations where you’d want to enter a number in a cell based on the contents of another cell. One was for a table with team names—Green, Blue, and Red—whose data needs to be sorted in that order. To do that, I described making a “Sort” column that uses a nested IF statement to “translate” each team name into a number, so that you can sort the team names as you desire.

If there were only two teams, this formula in the Sort column would do the job:

IF (B2="Green",1,2)

A Green row would have a 1 in the Sort column, while any not-Green row would be assigned a 2. But we need to consider that a not-Green row might be Blue or Red, and we want to identify them. So, we replace the 2 in the formula with another IF statement:

IF (B2="Green",1,IF(B2="Blue",2,3))

The original part of the formula says If B2=“Green” then put a 1 in the cell, otherwise…. The “otherwise” is now the next IF statement, which knows the cell doesn’t have Green in it (that was already handled) so it says If B2=“Blue” then put a 2 in the cell, otherwise put a 3 in it.

After checking for (and failing to find) Green in the first test (B2="Green"), or Blue in the second test (B2="Blue"), the only value possible is Red, so Red is assigned 3—the last “otherwise” value—by default, without a specific test for it.

Just for fun, here’s a chance to wrap your head around a doubly-nested IF statement. In a table that tracks your baseball card collection, you have four rankings: Excellent, Very Good, Good, and Poor. You want to be able to sort according to those rankings, but alphabetization doesn’t work, so you assign numbers in a column that you will sort by (as generally described in Sort Unsortables). This formula uses the same method as just described above, but includes an additional IF statement to handle the extra data. As in the previous example, the nested IFs describe all but one of the possibilities; if the last one (“Poor”) is in the referenced cell, it is assigned a 4 by default in the final IF:

IF(A1="Excellent",1,IF(A1="Very Good",2,IF(A1="Good",3,4)))