Introduction to Logic Functions in Microsoft Excel
pdf
keyboard_arrow_up
School
McGill University *
*We aren’t endorsed by this school
Course
331
Subject
Philosophy
Date
Jan 9, 2024
Type
Pages
12
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.
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
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.
Page 6 9B16E013
Solution: If the desired output is text, the text must be entered within quotation marks. Check that the model works properly by entering the time and distance for another day when you ran 8 km in 30 minutes. Nested IF Functions Just as it is possible to “nest” logic functions within other logic functions (place one function within another function), it is possible to place an IF function within another IF function to evaluate a sequence of conditional statements. Example 4: A middle-school fitness challenge allows students to earn one of three award levels for each fitness challenge: Gold, Silver, or Bronze. For the long jump activity, students are awarded Gold if they jump farther than 5 metres, they are awarded Silver for jumping at least 4.25 metres but less than 5 metres, and they are awarded Bronze for a jump distance that is at least 3.5 metres but less than 4.25 metres. For a given list of students, set up a spreadsheet model to assign their award level for long jump. 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
Page 7 9B16E013
Solution: It can be helpful to visualize complex logic problems by using a conceptual model. On the left, we present a flowchart of the logic. On the right, we present the Excel formula that maps to each stage of the flowchart. Instead of writing “No award,” we can force the cell to appear blank by just entering empty quotation marks (i.e., “ ”). Also, instead of hard-coding the words “Gold,” “Silver,” and “Bronze” into each formula, we can link to the labels in the inputs section. Soft-coding the labels is a good practice because it makes it easier to fix spelling mistakes and to relabel the categories. Distance ≥ 5 m?
Gold Distance ≥ 4.25 m?
Silver Distance ≥ 3.5 m?
Bronze No award =IF( Distance >= 5, “Gold”, ) IF( Distance >= 4.25, “Silver”, ) IF( Distance >= 3.5, “Bronze”, “No award”) True False True False True False 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 8 9B16E013
Warning: Excel will let you nest many IF functions within other IF functions. We highly recommend you avoid having more than two or three levels in one formula. After two or three levels, the IF functions are difficult to read for formula checking or spreadsheet auditing, and are difficult to fix if there is a bug (i.e., a programming error). If more than three levels are necessary in your specific problem, try to separate your work into two (or more) intermediate calculations and then combine your work. Dividing your work into easier-to-follow and easier-to-debug intermediate components has many advantages and is generally a best practice of spreadsheet modelling. Example 5: Consider ABC Co., a company with three main product lines. The company has just released new rules for earning a bonus. According to these new rules, sales agents who meet or exceed the sales targets for any two of the three product lines will receive a bonus equal to 3 per cent of total sales. Identify the bonus to be paid to each of the sales agents. Solution: The answer to this problem can be achieved in one step (demonstrated below), but doing so would lead to a poorly designed spreadsheet. The resulting formula is difficult both to read and to debug. Example of a solution that does not
follow best modelling practices: A much better modelling practice is to separate intermediate calculations into their own columns. This approach enables the analyst to see more easily which, if any, part of the formula is not working correctly. To answer this question, it is helpful to do each of the following calculations in its own column: (1) calculate total sales; (2) determine whether an agent exceeds the sales target for Product A, Product B, and Product C using an IF statement, reporting a 0 if the target is not met and a 1 if the target is met; and (3) use an IF statement to determine whether the bonus has been earned, and if a bonus has been earned, report the bonus. 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 9 9B16E013
Steps 1 and 2: Step 3: SUMMARY The purpose of this note is to introduce and illustrate the use of basic logic functions in Excel. Logic functions are useful for incorporating conditional events into spreadsheet models. Many real-world problems would be very difficult to model without the use of logic functions. PRACTICE PROBLEMS 1.
Rebecca’s car insurance has a $500 deductible. If the total damage is less than $500, she personally pays for the entire repair bill; otherwise, the insurance company pays the amount greater than $500. a.
Create an Excel model for Rebecca using good modelling practices. b.
Write an IF function that will calculate the amount Rebecca owes for a given damage amount. 2.
Dave runs a pizza shop on Main Street. His employees earn $12 per hour for regular time and $18 per hour for overtime. The overtime rate must be applied for any hours worked that exceed 44 hours per week. a.
Create an Excel model that informs Dave how much he owes an employee, using good modelling practices. b.
Use an IF function to help Dave calculate what he owes his employees, given the amount of hours they have worked. c.
Calculate the amount Dave owes the following workers: 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
Page 10 9B16E013
Employee Hours Serena 16 Adeline 39 Julian 46 Rayan 50 Jamal 24 Mona 48 Sera 39 Paval 47 Mariam 51 Jason 49 3.
Vladimir owns a store that sells rare and exotic lizards to reptile pet owners. He usually sells each lizard for $25. Due to the amount of time required for lizard breeding and the uncertainty of demand, he has two suppliers who offer two different prices. The low-cost supplier can supply a maximum of 50 lizards per month at $5 per lizard. Beyond this demand, Vladimir must go to his second supplier who can provide a seemingly unlimited number of lizards for $10 per lizard. a.
Build a model to calculate Vladimir’s monthly profit using good modelling practices. b.
How much profit does Vladimir make when he sells 30 lizards? c.
How much profit does Vladimir make when he sells 70 lizards? 4.
Sabina owns the dog-walking business Pooch Patrol. She charges $20 to walk one dog for 30 minutes, and $30 to walk one dog for an hour. She charges $30 to walk two dogs for 30 minutes and $45 to walk two dogs for an hour. Calculate her daily revenue for her regular schedule: Customer # # Dogs Time (in minutes) 1 1 30 2 1 60 3 2 30 4 1 60 5 2 60 6 1 30 7 1 30 8 2 60 9 2 30 10 1 60 11 1 30 12 2 30 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 11 9B16E013
PRACTICE SOLUTIONS 1. 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 12 9B16E013
3. b) Vladimir makes $600 in profit when he sells 30 lizards. c) Vladimir makes $1,300 in profit when he sells 70 lizards. 4. 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