Chapter 7 - Spreadsheets: Relational & Boolean Operators

Using Spreadsheets to Solve Problems

1.7 - Relational Operators & Boolean Functions

Thus far,the formulas written in this text have used combinations of arithmetic operators and functionsto produce a numeric value as a result. Using the concepts we’ve learned so far we can answer questions like “How much was last year’s income?” and “How much was this year’s income?” with numerical values. However, the arithmetic operators and functions we’ve learned so far do not allow us to answer questions like: “Was last year’s income greater than this year’s income?” In this chapter, we will learn operators and functions that allow us to decide whether or not a statement is true or false; we can then answer the previous question by deciding whether or not the phrase “Last year’s income is greater than this year’s income” is true. TRUEandFALSE are referred to as Boolean values and can be calculated using Excel formulas and functions.

Often when analyzing data, the decision about what to do next depends on whether or not the data meets specific criteria. For example, if we are calculating employees’ monthly paychecks, an employee may deserve a bonus if their sales exceeded a certain amount. In the previous chapter, the SUMIF and COUNTIF functions were used to count or sum data depending on whether or nota criterion was met. This chapter expands this ability to decide whether or not data meets criteria by introducing Relational operators and Boolean functions.

Relational Operators

The simplest way to calculate a Boolean value is to use a relational operator. Relational operators compare two values to determine if the relational expression is TRUE or FALSE. Much like arithmetic operators (+, -, *, /, etc.), relational operators appear between two operands, which can either be numerical constants, Boolean values, text values, cell references, or nested formulas/functions. A relational expression is a relational operator with two operands. e.g., 3=3, 5>2, A4<=SUM(A8:A20), etc.

Note: Be careful to distinguish the values TRUEand FALSEfrom the labels “True” and “False”. As with numbers, the computer treats these differently in formulas.

Using Relational Operators in Formulas

To determine the total of three plus five in Excel, the formula =3+5 can be used. The formula begins with an equal sign and is followed by the appropriate operands and operator. To determine whether or not it is true or falsethat 3+5 is equal to 8 use a relational operator:

=3+5=8

An ‘=8’ has been added to the formula. The first equal sign tells Excel that this is a formula, while the second equal sign is interpreted as a relational operator. Excel will first evaluate in order of precedence all parentheses, exponents and arithmetic operators before evaluating the relational operation. That is, the relational operators are last in the order of precedence (refer to Chapter 1-1 for a listing of the order of precedence). In this case the formula will first add 3 plus 5 to get 8, and then compare to see if =8=8. The resulting value will be TRUE.

There are six different relational operators that can be used. Examples of each are listed in the following table:

Table 1: Relational Operators
Description / Operator / Example / Resulting Value
Equal to / = / = 3+5=8 / TRUE
Not equal to / = Sum(3,7)>10 / FALSE
Greater than / =100>Max(5,10,20) / TRUE
Less than / = “B”“A” / FALSE
Greater than or equal to / >= / =B2=2 where B2 contains the value 2 / TRUE
Less than or equal to / <= / = 99<=8*3 / FALSE

Be especially aware of the order of precedence in which these relational expressions are evaluated. As an example, theformula =B3^2>10*Sum(A1:A10) would be evaluated as follows:

• First the computer would evaluate SUM(A1:A10).

• Next the computer would evaluate B3^2 (^ is the exponent symbol so B2^2 is the mathematical expression B32)
• The result of SUM(A1:A10) would then be multiplied by 10.
• Finally the two sides of the relational expression would be compared.

An Example

Consider the spreadsheet in Figure 1.This worksheet lists the costof trips to Boston and New Yorkbroken down into the costs of Food, Lodging, and Travel. An initial budget amount is listed in cell B1.

Question #1: Write an Excel formula in cell D4, which can be copied down the column, to determine if this cost component is more for the Boston trip than the New York trip.

• Compare the corresponding two values to get a Boolean value. i.e., is it true or false that 110 (food for Boston trip) is greater than 250 (food for NY trip)? This can be implemented using the greater than relational operator.
• Translate into Excel syntax. The cost in Boston, C4, is greater than the cost in NY, B4. Hence, the formula would be =C4>B4. With relational operators,formulas can frequently be written in several ways;=B4<C4is mathematical equivalent to =C4>B4.
• Since the formula is being copied down the column, determine which references copy relativelyand which copy absolutely. In this case both referencescopy relatively.

Question #2: Write an Excel formula in cell B9 thatcan be copied across the row to determine if the total cost of this trip is within budget. For convenience the cell B1 has been namedbudget.

• Again, two values must be compared to get a Boolean logical result. Therefore a relational operator should be used. “Within budget” suggests that if the total value of this trip is less than or equal to the budget a TRUE value should be returned.
• In Excel syntax, the formula is =B7<=budget. Note how the named range is being used this formula.
• Since the formula is being copied across the row, consider which references copy relatively and which copy absolutely. The total value of the trip should change when the column is changed from NY to Boston (B7 becomes C7). The budgeted value remains the same so cell B1 should be referred to absolutely. In this case a previously assigned range name has been used to represent cell B1. Range names always copy absolutely, so no modifications are needed to this formula.

The final formula is =B7<=budget. If the range name were not used, the formula=B7<=$B1 would be required.

What not to do:

Note that placing an absolute sign in front of a ranged name ($budget) is incorrect syntax.

Question #3: Write an Excel formula in cell B10 that can be copied across the row to determine if travel is the largest component of the total cost for this trip.

• Here,the travel cost must be compared to the largest cost item to determine if they are equal. Since we are checking whether two values are equal,the equal (=) relational operator will be required. In this case, the largest cost item has not yet been determined, so this also will have to be calculated as part of the formula.
• In cell B12, the formula will begin with =B6, a beginning equal sign starting the formula and the value for travel to New York. This is then followed by a second equal sign for the relational comparison equal to. The last component of the equation is the “largest component of the total cost”. How can the largest cost component be determined from among the cells B4:B6? A MAX function finds the highest value from a list of values. The final formula would be =B6=Max(B4:B6).
• This formula is being copied across the row. Each of these cell references (B4 and B6) change relativelywhen copied across (C4 and C6) so no changes to the formula are required.

comparing text values

Relational expressions can also be used to compare text strings. Text strings are compared using alphabetical order, where numbers come before letters. A valid Excel formula could be =“Big”>“Apple”. Since the letter B comes alphabetically after the letter A, this expression would be evaluated as TRUE. Here the quotes are necessary; if the formula were written as =Big>Applethe computer will look for the range named Big and compare it to the range named Apple. If both of these range names exist then the formula would compare the values contained in those named ranges. Otherwise a #NAME! error will be displayed.

What about the formula =“BIG”=“big”?In Excel, capital and lower-case letters are equivalent for comparison purposes so the result of =“BIG”=“big” is TRUE. Formulas with relational expressions can also reference cells containing text; the formulas would be written in the same way as if the cells contained any other value. The formula =AB200<Z25 would evaluate to FALSE if cell AB200 contained the text string “hello” and the cell Z25 contained the text string “goodbye”.

BooleanFunctions

Relational operators can be used to compare two different values to determine if a relational expression is true or false. But how can one determine ifall items in a group meet a specifiedcriterion or if at least one item in a group meets a specified criterion? A list of Boolean values (TRUE/FALSE) can be evaluated using And, Or, & Not operations. For example is the value in cell B2 greater than 20 and the value in cell B3 greater than 20? In Excel these operations are performed using the functions AND, OR & NOT. These functions perform the following tasks:

• The AND function will evaluate a list of logical arguments (TRUE andFALSE values)and return TRUEif all of the arguments are TRUE. An AND function is FALSE if at least one of its arguments is FALSE.
• The OR function will evaluate a list of logical arguments (TRUE andFALSE values) and return TRUE if at least oneof the arguments is TRUE. The OR function is only FALSEif all of the arguments in the function are FALSE.
• The NOT function will evaluate only one logical argument (either a TRUE or FALSE)return TRUE if it is FALSE. The NOT function essentially changes the value TRUEto FALSEor the value FALSEto TRUE.

The AND Function syntax

The AND function will evaluate a list of logical arguments to determine if they are allTRUE. Arguments may consist of any combinations of cell references, values, and ranges such that each reduce to a single TRUE or FALSE value. An AND function is FALSE if at least oneargument is FALSE.The syntax of the AND function is as follows:

And(logical1, logical2,….).Consider the following examples:

Formula / Value / Description
AND(TRUE, TRUE, TRUE) / TRUE / The arguments include a list of Boolean values directly entered as function arguments. Since all of the arguments are TRUE the final value is TRUE.
AND(25>24, 3<=2+1) / TRUE / The arguments contain nested Relational expressions. As 25>24 is TRUE and 3<=2+1 is TRUE the formula will be reduced to =AND(TRUE,TRUE) and then finally to TRUE.
AND(A1:A3)where cell A1 contains the value FALSE, and cells A2 & A3 contain the value TRUE / FALSE / The argument listed is a range of cell references that contain TRUE/FALSE values. Since at least one logical argument is FALSE, the final value is FALSE.
AND(A1,A5<A4,MIN(A1:A5)=2)where cell A1 contains the value FALSE / FALSE / The 1st argument is a cell reference to a cell with a Boolean value, the 2ndcontains a nested relational expression containing cell references and the 3rd argument is a relational expression including a nested function. Since A1 contains the value FALSE, the result of the function is FALSE.

Using The AND Function

Consider the following spreadsheet in Figure 2. This spreadsheet lists travel component costs for a single trip to New York. Each cost component is listed with a budget amount, an actual cost, and a category (optional or required). An item is‘O’ if it is an optional item and ‘R’ if it is a required item.

Question #1:Write an Excel formula in cell E2 that can be copied down the column to determine if this item is within Budget.

• The question requires comparing the actual cost of food ($250) with the budgeted value ($200). “Within budget” implies that the actual cost be less than or equal to the budgeted amount.
• Since only two values are being compared, only a relational operator will be needed to implement this: $250<=$200. In Excel syntax this would be =D2<=C2.
• Both operands D2 and C2 copy relatively.

Question #2:Write an Excel formula in cell E9 to determine (TRUE/FALSE) ifall cost items are within budget.

• When determining if allitems meet specific criteria from a list, an And operation is required. It has already been determined in cells E2:E6 whether or noteach specific cost itemis within budget. If any one of these values is FALSE, the resulting value should be FALSE.
• To implement an And operation, use the AND function. Using cells E2:E6, which already contain TRUE/FALSE values, an Excel formula can be written as follows:=AND(E2:E6).
• Since this formula is not being copied, absolute cell referencing need not be considered.

What value should be displayed in cell E9 as a result of this formula? Substituting the Boolean values from cells E2:E6 into the function results in the formula =AND(FALSE, FALSE, TRUE, TRUE, TRUE). This reduces to the value FALSE since at least one argument is FALSE.

What not to do:
Using Ranges with Relational Operators:
Instead of using the values solved for in question #1, could the formula be written to directly include the relational expressions? The answer is yes, but the format is very specific. If using relational expressions within an AND function, each individual comparison must be listed separately: =AND(D2<=C2,D3<=C3,D4<=C4,D5<=C5,D6<=C6). Clearly this is cumbersome and therefore not recommended.
Why not write the formula =AND(D2:D6<=C2:C6), using ranges on either side of the relational operator. This looks simple and easy to understand. However this is not a valid syntax and therefore will either result in a #Value! error or an incorrect result. Can you compare a range of values to a single value such as =AND(D2:D6>100)? No, this too is invalid.
These are common mistakes, but the reason why each is invalid syntax is clear: the meaning of such an expression is ambiguous, even to the user. For example, what would D2:D6<=C2:C6 mean? Would it mean that all values in D2:D6 are less than or equal to all values in C2:C6? Or the sum of the values in D2:D6 is less than or equal to the sum of the values in C2:C6? Would it mean that the value in D2 is less than or equal to the value in C2 and D3 is less than or equal to C3, etc?
One should think of relational operators the same way they think of arithmetic operators. Just as one would never write A2:A8+1, one should never write A2:A8>=1.
If a worksheet has a long list of items that need to be compared to a corresponding criterion to determine if they all meet their respective criterion, it is more efficient to first calculate a simple relational expression as in question 1, and then write an expression similar to the one in question 2. More advanced users can learn about working with arrays in Excel to perform these types of tasks.
Listing each cell reference individually:
Another validway to write this formula is =AND(E2,E3,E4,E5,E6). In cases with non-contiguous ranges this method works well. However for continuous ranges, it is recommended (just as in the case of SUM, MAX, MIN, etc.) that a range such as E2:E6 be used.

Question #3: Write an Excel formula in cell E10 to determine if all optional items are within budget.

• Since both Tours and Souvenirs are optional they would both have to be within budget for this statement to be TRUE. To obtain a TRUE value if both individual items are TRUE, an And operation is required.
• Using the AND function and the corresponding cell references for determining whether a cost is within in budget, the resulting formula is=And(E4,E5). Notice that the problem does not automatically determine which items are optional. The problem will require the writer to first check column B to determine if the cost category is optional (O), and then if it is, write the corresponding cell reference in column E which contains the TRUE/FALSE values for within budget.
• As this formula is not copied, absolute cell referencing need not be considered.

Question #4: Write an Excel formula in cell E11 (not shown) to determine if the total cost of the NY trip is not more than $100 over budget and that the Tours component is the smallest component.

• In this problem there are several sets of criteria which we need to be analyzed:
• The trip is not more than $100 over budget. This can be represented mathematically as: actual value <= budget +100.
• The tours component is the smallest component. This can be determined by finding the minimum value in a given range and then comparing it to the tours value: =ActualTour value = minimum component value. If the Tours value is equal to the minimum value, the statement is TRUE.

Since both of the above criteria must be TRUE for our statement to be TRUE, an And operation will be needed.

• In Excel syntax these can be represented as follows:
• D7<=C7+100
• D4=MIN(D2:D6)

Combining these two expression with an AND function results in theformula AND(D7<=C7+100,D4=MIN(D2:D6)). Will this formula result in a TRUE or a FALSE value?

• Again since this formula is not copied, absolute referencing need not be considered.

Be careful when working with complex formulas and nested functions that the parentheses used exactly match: there must be an equal number of opening and closing parenthesis and they must be in the proper locations. Failure to do so can result in either error messages and/or invalid results.

The Or Function Syntax

TheORfunction will evaluate a list of logical arguments to determine if at least one argument is TRUE. As with the AND function, arguments may consist of any combination of values, text strings, cell references or ranges such that each reduce to a TRUE or FALSE value. The syntax of the OR function is as follows:

Or(logical1, logical2,….).

An Or function is only false if all arguments are false. Some Examples of using the OR function are listed in the following table:

Formula / Value / Description
OR(FALSE, FALSE, FALSE) / FALSE / The arguments include a list of Boolean values directly entered as function arguments. Since all of the arguments are FALSE the final value is FALSE.
OR(25>24, 3<2+1) / TRUE / The arguments contain nested Relational expressions. As 25>24 is TRUE and 3<2+1 is FALSE the formula will be reduced to =OR(TRUE,FALSE) and then finally to TRUE.
OR(A1:A3)where cell A1 contains the value TRUE, and cells A2 & A3 contain the value FALSE / TRUE / The argument listed is a range of cell references that contain TRUE/FALSE values. Since at least one argument is TRUE, the final value is TRUE.
OR(A1,A5<A4,MIN(A1:A5)=2) where cell A1 contains the value TRUE. / TRUE / The 1st argument is a cell reference to a cell with a Boolean value, the 2nd contains a nested relational expression containing cell references, and the 3rd argument is a relational expression including a nested function. Since cell A1 contains the value TRUE, the result of the function is TRUE.

Using The OR Function

Consider the spreadsheet seen inFigure 3. The spreadsheet is almost identical to the one from the previous example. Use this worksheet to answer the following questions.