Microsoft

Logic in Excel

Using Logical Operators[footnoteRef:1] [1: www.fiveminutelessons.com]

A lot of work in Excel involves comparing data in different cells. When you make a comparison between two values, you want to know one of these things:

· Is value A equal to value B (A=B)

· Is A greater than B (A>B)

· Is A less than B (A<B)

· Is A greater than or equal to B (A>=B)

· Is A less than or equal to B (A<=B)

· Is A not equal to B (A<>B)

These are called logical or Boolean operators because there can only be two possible answers in any given case - TRUE or FALSE.

Using Logical Operators in your formulas

Excel is very flexible in the way that these logical operators can be used. For example, you can use them to compare two cells, or compare the results of one or more formulas. For example:

· =A1=A2

· =A1=(A2*5)

· =(A1*10)<=(A2/5)

As these examples suggest, you can type these directly into a cell in Excel and have Excel calculate the results of the formula just as it would do with any formula. With these formulas, Excel will always return either TRUE or FALSE as the result in the cell.

A common use of logical operators is found in Excel's IF function. The IF function works like this:

=IF(logical_test,value_if_TRUE,value_if_FALSE)

In essence, the IF function carries out a logical test (the three examples above are all logical tests) and then return the appropriate result depending on whether the result of the test is true or false. For example:

· =IF(A1>A2,"Greater than","Less than")

· =IF(A1>A2,A1*10%,A1*5%)

However, you don't always need to use an IF formula. Here's a version of this formula that uses a logical operator, and also demonstrates another useful feature of logical operators in general:

· =(A1>A2)*(A1*10*)+(A1<=A2)*(A1*5%)

It looks confusing, but in fact it is very logical (excuse the pun). However, it helps to know that in Excel, TRUE is the same as 1, and FALSE is the same as 0.

So, in this example…

If A1>A2 is TRUE, then the formula will multiple (A1*10%) by 1.

Because A1>A2 is TRUE then A1<=A2 is false, so it will then multiply (A1*5%) by 0.

It will then add the results together: (A1*10%)*1 + (A1*5%)*0.

The result is whatever (A1*10%) equals in the specific example.

Obviously, if A1 is less than A2, then the reverse of this would occur.

Using Multiple Logical Operators

In some cases, you may want to perform more than one comparison as part of your formula. For example:

· (Today is Wednesday) and (Sky is Blue)

· (Today is Wednesday) or (Sky is Blue)

· (Today is Wednesday and (Sky is NOT Blue)

· (Today is Wednesday) or (Sky is NOT Blue)

In Excel, you can use one of three logical functions to construct these formulas:

· AND

· OR

· NOT

The AND function works by performing multiple comparison tests and then returning TRUE if all of the tests were true, and FALSE if one or more of the tests were false. Here are a couple of examples:

· =AND(A1>A2,A1<A3) (if A1 is greater than A2 AND less than A3, then return TRUE otherwise return FALSE)

· IF(AND(A2>A2,A1<A3),"Both are true","At least one is false") (this IF function will return one of the two values depending on whether the AND function returns TRUE or FALSE).

The OR function works in a very similar way to the AND function. However, whereas AND requires that all tests return true, the OR function will return TRUE if only one of the tests return true. For example:

· =OR(A1>A2,A1<A3) (if either A1>A2 OR A1>A3 is true, then return TRUE. If neither are true, return FALSE).

· =IF(OR(A1>A2,A1<A3),"One or both are true","Neither are true")

It is important to note that the AND the IF functions can both incorporate up to 255 logical tests (my examples here have only used 2). Regardless of the number of tests you include, the same rules apply as they did in my simple examples.

It is also worth noting that you can combine the AND and OR functions in a single formula. For example:

· =AND(OR(A1>A2,A1<A3),A1>A4)

In this example, the AND function will only return TRUE if either (A1>A2 OR A1<A3) AND A1>A4

The final logical function you can use is the NOT function. The NOT function is somewhat self-explanatory - it takes any logical test result and does the opposite. For example:

· =(Sky is Blue) - will return TRUE if the sky is blue, and FALSE if the sky is not blue.

· =NOT(Sky is Blue) will return FALSE if the sky is blue, and TRUE if the sky is not blue.

Note that this example doesn't care what other colors the sky might be!

Of course, you can use the NOT function with the AND, OR and IF functions:

· =NOT(AND(A1>A2,A1<A3)) - if A1>A2 AND A1<A3, then return FALSE

· =AND(NOT(A1>A2),A1<A3) - if A1 is NOT >A2 AND A1<A3 then return TRUE.

Note that writing NOT(A1>A2) is another way of writing (A1<=A2). In this simple example, using a NOT function didn't add much value, but in some cases the NOT function can be very handy.

In summary, a lot of what you do in Excel, particularly once you start using IF functions, involves using logical operators. The logical functions, AND, OR and NOT are a great way to extend your use of logical operators to perform more complex calculations.

Quick Reference

Logical Operators

< less than

Example: =(A1<A2) – Returns true if A1 is less than A2.

> greater than

Example: =(A1>A2) – Returns true if A1 is greater than A2.

<= less than or equal to

Example: =(A1<=A2) – Returns true if A1 is less than or equal to A2.

>= greater than or equal to

Example: =(A1>=A2) – Returns true if A1 is greater than or equal to A2.

<> not equal

Example: =(A1<>A2) – Returns true if A1 is not equal to A2.

= equal

Example: =(A1=A2) – Returns true if A1 is equal to A2.

Logical functions in Excel

AND – Logical “and”. Returns true if all arguments evaluate to true.

Example: =AND(A1>A2, B1<B2) – Returns true if cell A1 is greater than A2 AND cell B1 is less than B2.

OR – Logical “or”. Returns true if all arguments evaluate to true.

Example: =OR(A1>A2, B1<B2) – Returns true if cell A1 is greater than A2 OR cell B1 is less than B2.

XOR – Exclusive “or”. Returns true if one, but not both, of the arguments are true.

Example: =XOR(A1>A2, B1<B2) – Returns true if cell A1 is greater than A2 OR cell B1 is less than B2 but not both.

NOT – Logical “not”. Returns the reversed value of the argument.

Example: = NOT(A1>A2) – Returns true if cell A1 is NOT greater than A2. Not that this is not logically equivalent to A1<A2.

IF – Returns the first preset value if an argument is true, the second if it is false.

Example: =IF(A1>100,10,0) – Returns 10 if A1 is greater than 100, 0 if it is not.

Lab Instructions

Download the Bob’s Dice Shop Worksheet

1. Using the above functions, write a formula in the cell to the right of each proposition that will evaluate the proposition to true or false. Do not answer true or false, you must write a formula that returns the correct answer. No credit will be given for manually evaluating the statement and typing true or false.

2. Bob receives a bonus of $50 from the d20 manufacturer each time his sales exceed $500 in a quarter. Write a conditional statement (IF) in cell B11 that will display 50 if the d20 sales are greater than $500 and 0 otherwise. Once you have the formula correct, copy it into C11-E11.

3. Bob need to order extra of any type of dice that sold more than $650 for the year. Write a conditional statement in cell G4 that displays “Reorder!” if the sales were more than $650 and “Hold” if the sales were $650 or less. Once you have the formula correct, copy it into G5 – G9.

4. Extra Credit: Use conditional logic to create a conditional formatting rule that will color every other row light green.

5. Save the worksheet as FirstnameLastnameHW28 and submit to the homework 18 drop box.