Introduction to Logic Functions in Microsoft Excel

.pdf

School

McGill University *

*We aren’t endorsed by this school

Course

331

Subject

Philosophy

Date

Jan 9, 2024

Type

pdf

Pages

Uploaded by MasterTeam12658

W16409 INTRODUCTION TO LOGIC FUNCTIONS IN MICROSOFT EXCEL Kyle Maclean and John Lyons wrote this note under the supervision of Lauren E. Cipriano and Gregory S. Zaric solely to provide material for class discussion. The authors do not intend to provide legal, tax, accounting or other professional advice. Such advice should be obtained from a qualified professional. This publication may not be transmitted, photocopied, digitized or otherwise reproduced in any form or by any means without the permission of the copyright holder. Reproduction of this material is not covered under authorization by any reproduction rights organization. To order copies or request permission to reproduce materials, contact Ivey Publishing, Ivey Business School, Western University, London, Ontario, Canada, N6G 0N1; (t) 519.661.3208; (e) cases@ivey.ca; www.iveycases.com. Copyright © 2016, Richard Ivey School of Business Foundation Version: 2017-01-23 The purpose of this note is to introduce the concept of logical statements in Microsoft Excel and to illustrate the use of Excel’s built-in logic functions. Excel’s logic functions enable situations in which the formula in a cell, not just the value of a cell, depends on the value in one or more other cells. For example, one rate of pay may apply if an employee works fewer than 40 hours in a week and another rate of pay may apply to hours worked in excess of 40 hours. Another example is the payout on a call option. The payout is zero when the value of the stock is less than the strike price; otherwise, the payout increases in value with the stock price. Examples such as these are common throughout business, making Excel’s logic functions very useful. Upon completion of this note and its associated activities, you will be able to do the following: • Compare values using =, >, and < • Use the built-in functions in Excel to evaluate compound logical statements (such as AND and OR) • Use the IF function to produce a specific output in response to a true or false outcome resulting from a logical assessment COMPARISONS In many instances, you may want to know whether one value is greater than, equal to, or less than the value of another cell. These individual comparisons are easy to make, but to complete the task in a spreadsheet, potentially over numerous data observations, requires the information to be set up in a very particular way. Consider, for example, wanting to identify all the employees who worked overtime hours in a week, the sales agents whose sales exceeded a target level, or whether machine downtime exceeded the levels necessary for efficient production. Excel can use logical statements to compare the contents, the values, or text of two or more cells. For the exclusive use of Z. Qian, 2022. This document is authorized for use only by Zekun Qian in MGCR-331-Fall2022-Excel-HBP-Coursepack-7-Articles taught by Sol Tanguay, McGill University from Aug 2022 to Feb 2023.

Page 2 9B16E013 The outcome of a logical statement is True or False. For example, take note of the following logical statements and their outcomes: Logical Statement True/False 37 = 40 False 37 < 40 True 37 > 40 False To perform these comparisons in Excel, you first type = to indicate that the cell requires a formula to be evaluated, and then type an expression for the comparison you want to evaluate. To assess the logical statement “Does 37 equal 40?” type =37=40 into a cell and press Enter. In this equation, the first = sign indicates that you are entering a formula, and the second = sign indicates that you are making an “equal to” comparison. As expected, this formula evaluates to FALSE. Similarly, we can assess the logical statement “Is 37 less than 40?” by typing =37<40. Again, Excel is able to perform the logical assessment and will return TRUE. Logical statements can use mathematical operations or formulas on either side of the comparison operator. For example =5*5>20 would evaluate to TRUE, because Excel would first evaluate 5 × 5 = 25, which is greater than 20. Summary of Logical Operators Symbol Usage Example Result = Equal to =7=3 FALSE > Greater than =5>3 TRUE >= Greater than or equal to =3>=3 TRUE < Less than =6<3 FALSE <= Less than or equal to =7<=7 TRUE <> Not equal to =7 <> 8 TRUE Press Enter Press Enter For the exclusive use of Z. Qian, 2022. This document is authorized for use only by Zekun Qian in MGCR-331-Fall2022-Excel-HBP-Coursepack-7-Articles taught by Sol Tanguay, McGill University from Aug 2022 to Feb 2023.

Page 3 9B16E013 Example 1: Tina is the manager of a sales department. Each week, Tina sends an encouraging email to all sales agents who sell more than $5,000. Identify which sales agents should receive an email this week. Solution: This task can be accomplished using the > symbol. In cell C4, type =B4>$B$1. In plain language, this formula is equivalent to asking, “Is the value in cell B4 greater than the value in cell B1?” Notice that we used absolute referencing (i.e., $B$1) when linking to the cell containing the sales threshold. This approach allows us to quickly copy and paste the formula down the column, such that the B4 reference will become B5 in row 5, B6 in row 6, and B7 in row 7, but the reference to B1 will stay constant in all rows. We check that we have done the referencing correctly by looking at the formula in cell C7, which should be =B7>$B$1. COMPARISONS USING LOGIC FUNCTIONS Excel is also capable of evaluating more complex logical statements using the built-in functions AND and OR. The AND Function AND(logical1, [logical2], . . .) is a function that evaluates to TRUE only if all of the logical statements included are true. As a simple example, consider the statement “Is 2 greater than 3 and 5 greater than 4?” To evaluate this compound logical statement in Excel, type =AND(2>3, 5>4). Excel evaluates each of the logical For the exclusive use of Z. Qian, 2022. This document is authorized for use only by Zekun Qian in MGCR-331-Fall2022-Excel-HBP-Coursepack-7-Articles taught by Sol Tanguay, McGill University from Aug 2022 to Feb 2023.

Page 4 9B16E013 statements and will return TRUE only if both logical statements are true. In this case, because 2>3 is false, Excel will return FALSE. The OR function OR(logical1, [logical2], . . .) is a function that evaluates to TRUE if any of the logical statements are true. As a simple example, consider the statement “Is 2 greater than 3 or 5 greater than 4?” To evaluate this compound logical statement in Excel, type =OR(2>3, 5>4). Excel evaluates each of the logical statements and will return TRUE if any statement is true. In this case, because 5>4 is true, Excel will return TRUE. Example 2: Consider ABC Co., a company with three main product lines. Each month, the sales manager reviews whether his sales team is reaching specific product-line targets. Sales agents who meet or exceed sales targets for all three product lines will receive a bonus. Identify which sales agents will be paid the bonus. Solution: This task can be accomplished using AND to evaluate each of the three comparisons that must be true to earn the bonus. In cell E6, type =AND(B6>=$B$2, C6>=$C$2, D6>=$D$2). For the exclusive use of Z. Qian, 2022. This document is authorized for use only by Zekun Qian in MGCR-331-Fall2022-Excel-HBP-Coursepack-7-Articles taught by Sol Tanguay, McGill University from Aug 2022 to Feb 2023.

Page 5 9B16E013 Summary of Logic Functions Function Usage Example Result AND All of the logical statements are true =AND(3=3, 2<8) TRUE OR Any of the logical statements are true =OR(3>=5, 7<=1) FALSE THE IF FUNCTION An output of TRUE or FALSE will not allow us to address either of the two examples at the beginning of this note: to calculate pay incorporating the possibility of overtime, or to calculate the payout of a call option. The IF function allows the user to control the output of a logic test, so that the value is one thing if the logic test is true and the value is something else if the logic test is false. IF(logical_test, [value_if_true], [value_if_false]) is a function that allows the user to specify the preferred output if the logical statement is either true or false. Returning to an example we have considered before, we want to evaluate the logical statement “Is 37 less than 40?” However, rather than an outcome of TRUE or FALSE, we want the answer to be 1 (TRUE) or 0 (FALSE). This type of output is particularly useful when the outcome of this test needs to be built into further calculations. Example 3: You enjoy running, and each day you take a different route. Build a spreadsheet model to compare your speed to a target threshold of 4 minutes per kilometre (km). If you achieve your target speed, you want to give yourself an encouraging message, “Good job!” If you do not achieve your target speed, you want the output to read “Not quite. Try again next time.” Press Enter For the exclusive use of Z. Qian, 2022. This document is authorized for use only by Zekun Qian in MGCR-331-Fall2022-Excel-HBP-Coursepack-7-Articles taught by Sol Tanguay, McGill University from Aug 2022 to Feb 2023.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help