Chapter 5 – Logic Functions

INTRODUCTION

In this chapter, we explore the ability of Excel to make choices for us based on other data. We do this with logic functions where the result of one cell can be different things depending on the value in another cell.

  • Use the IF function
  • Use IF in conjunction with many other functions to solve problems

Guided Exercises

Open the Chapter05_LogicFunctions.xlsx file.

TestScores&Fundraiser worksheet

You see data related to students’ test scores and how much money they raised for a fundraiser.

  1. The requirement for successfully passing the test is a score of 70 or higher. In cell D4, use an IF function to determine whether each student passed or not. If the student passed, then the word Pass should be displayed. Otherwise, the cell should be left blank. Fill your function down column D.
  2. The school challenged every student to raise at least $50 for the fundraiser. In cell G4, use an IF function to determine whether each student met that goal or not. If a student raised at least $50, then the word Yes should be displayed. Otherwise, the word No should be displayed. Fill your function down column G.
  3. In cells I3, I5, and I7, use functions to answer each of the questions.

Furniture worksheet

This sheet has data from the sales of various furniture pieces at the No Obstacles HOME store during 2015. The Revenue from each transaction has already been calculated for you beginning in cell E8, and the GrandTotal calculation for each transaction, beginning in G8, will include the Shipping Fee once you figure out if each transaction should be charged for shipping or not.

  1. If the Revenue from the transaction is at least the amount in cell D4, then the shipping fee is 0. Otherwise, the shipping fee is based on the percentage charged for shipping as indicated in cell D5. In cell F8, write a function that will determine the shipping fee for each transaction. Round the value to the nearest penny and format as Currency. Fill the formula down column F to calculate the correct shipping fee for all transactions.
  2. No Obstacles is considering offering discounts to the Preferred Customer, identified in G4, and to customers who purchased at least 10 items in a single transaction. In cell H8, write a function that will identify who these customers are. If they are a preferred customer or if they bought at least 10 items in one transaction, then they will get a 5% discount. Otherwise, the discount will be 0%. Format the value as Percentage with 0 decimal places displayed. Fill the function down column H to return the potential discount percent for each transaction.
  3. No Obstacles is also considering a discount of $100 for transactions where the price of the item is more than $300 and the customer bought more than 5 of that item in a single transaction. In cell I8, write a function that will identify which transactions would qualify for this $100 discount. If a transaction does not meet the requirements, the discount is $0. Format the value as Currency. Fill the function down column I to return the potential discount amount for each transaction under these circumstances.
  4. In cell J8, use a formula to calculate the NewGrandTotal for the transaction in row 8. This calculation should start with the GrandTotal in G8, then take off the discounts in H8 and I8. Fill the function down column J to return the NewGrandTotals for each transaction.

SchoolSupplies worksheet

  1. The Tax that needs to be charged for each transaction has not been calculated yet. In cell H3, use a function to determine how much tax is owed for each transaction. If the transaction occurred before 2017, the tax rate is 7% of the total. If the transaction occurred on or after January 1, 2017, then the tax rate is 8% of the total. Format the value as Currency. Fill the function down column H to calculate the tax owed for each transaction.
  2. Some of the orders should get Free Shipping. Orders that have a pre-tax total of $1,000 or more get free shipping, but so do orders of more than 50 binders. In cell I3, use a function to determine whether an order qualifies for Free Shipping. If it does, display the word Yes; if it doesn’t, the cell should be blank. Fill the function down column I to determine which orders get free shipping.
  3. In cell L3, use a function to return the number of transactions that the sales rep given in cell K3 had in the region given in cell L2. Write this function so it can be filled both down column L and across to column N to return each sales rep’s total pre-tax revenue for each region. Format the revenue values as Currency.

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.

More Practice

Work you should do before our next class

Open the Chapter05_LogicFunctions.xlsx file.

On the SalespersonBonuses worksheet:

  1. An employee earns a $50 bonus for each transaction that they completed during the holiday season. The holiday season is any date on or after the date in cell H2. In cell E4, use a function to determine which transactions earn the Holiday Bonus. If a transaction was completed on or after the date indicated in cell H2, then the Holiday Bonus is $50.00. Otherwise, the Holiday Bonus should be blank. Format the values as Currency, and fill the function down column E to return the Holiday Bonus for each qualifying transaction.
  2. An employee earns a $100 bonus for each transaction in which they sell at least 20 items. In cell F4, use a function to determine which transactions earn the Volume Bonus. If at least 20 items were sold in a transaction, then the Volume Bonus is $100.00. Otherwise the Volume Bonus should be blank. Format the values as Currency, and fill the function down column F to return the Volume Bonus for each qualifying transaction.
  3. In cell J4, use two SUMIFS functions to find the Total Bonus amount for the employee whose ID is given in cell I4. The first SUMIFS will add the Holiday Bonus values for the employee; the second SUMIFS will add the Volume Bonus values for the employee. The two SUMIFS functions will need to be added together to find the Total Bonus for the employee. This problem doesn’t work with just one SUMIFS function because there are two sets of numbers to add, not two criteria that need to be met. Format the values as Currency, and fill your functions down column J to return the Total Bonus for each employee.

On the EmployeeRaises worksheet:

  1. In Scenario 1, raises would go to employees who are at least 40 years old and who have worked for the company for at least 10 years. In cell H6, write a function to calculate the raise for employees who fit this description. If they qualify, then the RaiseAmount is based on the salary rate increase percentage in cell B2. Otherwise, their raise is 0. Round the value to 2 decimal places and format as Accounting. Fill the function through cell H17 to calculate correct raises for each person that qualifies in Scenario 1. Note: A value of 0 in the Accounting format will display as a hyphen. This is okay.
  2. In Scenario 2, raises would go to employees who are either in the Training department or who have worked for the company for less than 10 years. In cell Q6, write a function to calculate the raise for employees who fit this description. If they qualify, then the RaiseAmount is based on the salary rate increase percentage in cell B2. Otherwise, their raise is 0. Round the value to 2 decimal places and format as Accounting. Fill the function through cell Q17 to calculate correct raises for each person that qualifies in Scenario 2. Note: A value of 0 in the Accounting format will display as a hyphen. This is okay.
  3. In Scenario 3, raises would be awarded to all employees who are in the Sales department and all employees who have worked with the company for at least 15 years who are at least 55 years old. In cell H22, write a function that would calculate the NewSalary for employees who fit this description. If they qualify, then they get a $2,000 raise. All other employees get no raise; their salary would remain the same as it currently is. Format the values as Accounting. Fill the function through cell H33 to calculate the NewSalary for each person based on this scenario.
  4. In Scenario 4, raises would be awarded to everyone based on these conditions: Employees who are already earning at least $50,000 would receive a raise of $1,000. Employees who are earning less than $50,000 would receive a raise of $2,000. In cell Q22, write a function that correctly identifies how much of a raise each person should receive. Format the values as Accounting. Fill the function through cell Q33 to correctly calculate each person’s raise based on this scenario.
  5. An employee who is 60 years old or more is eligible for retirement. In cell H37, use a function to return either Yes or No to indicate whether or not each employee can retire.
  6. Employees who are under 30 years old or who have been employed for under 10 years should attend some training. In cell I37, use a function to return either Yes or No to indicate whether or not each employee needs to attend training.

The KitchenItems worksheet contains a list of transactions in which items from the Kitchen department were purchased.

  1. In cell H4, write a formula that will calculate the revenue for the product in row 4 based on the price in F4 and the Volume sold in G4. Fill your formula down to H48 to calculate the revenue for each transaction.
  2. In cell L3, use a function to find the Average Revenue of these transactions. Round the value to 2 decimal places. Format this value as Accounting.
  3. In cell I4, use a function to determine if the Revenue in row 4 is at or above the Average Revenue or not. If the revenue value is at or above the average revenue value, then the function should return the value Yes. Otherwise, the cell should remain blank. Fill the function down to determine whether each revenue amount in column H is at or above the average revenue amount in L3.
  4. In cell L5, use a function to find the number of transactions that were at or above the average revenue.
  5. In cell L8, use a function to find the number of transactions given on this worksheet.
  6. In cell L12, calculate the percent of transactions that were at or above the average revenue. This is the result of dividing the number of transactions at or above the average by the total number of transactions. Round the value to 4 decimal places. Format as a Percentage with 2 decimal places displayed.

 

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.