Chapter 9 – 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 Chapter09_LogicFunctions.xlsx file.
TestScores&Fundraiser worksheet
In the TestScores&Fundraiser worksheet, you see data related to students’ test scores and how much money they raised for a fundraiser.
- 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” (without the quotes) should be displayed. Otherwise, the cell should be left blank. Fill your function down column D.
- 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.
- In cells I3, I5, and I7, use functions to answer each of the questions.
Furniture worksheet
The Furniture sheet has data from the sales of various furniture pieces at furniture store during 2024. 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.
- If the Revenue from the transaction is at least the amount in cell B3, then the shipping fee is 0. Otherwise, the shipping fee is based on the percentage charged for shipping as indicated in cell B4. 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.
- This company is considering offering discounts to the Preferred Customer, identified in B5, 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.
- They are 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.
- 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
The SchoolSupplies worksheet represents mass orders for school supplies for a variety of regional sales for an office supply company, broken down with sales rep data (who made the sale from that company).
- 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 in H3 as Currency. Fill the function down column H to calculate the tax owed for each transaction.
- 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.
- 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 with the proper date modified timestamp.
More Practice
Work you should do before our next class
Open the Chapter09_LogicFunctions.xlsx file.
On the ConsultantBonuses worksheet:
- As a surprise, a consultant earns a $5 bonus for each feedback submission that they completed in less than 3 hours of work. In cell E4, use a function to determine which submissions earn the speed bonus of $5.00 (or blank if not). Format the values as Currency, and fill the function down column E to return the bonus for each qualifying feedback submission.
- An employee earns another bonus for each feedback that was deemed Negative to begin with. In cell F4, use a function to determine which submission earn the negative bonus of $2.00 (or blank if not). Format the values as Currency, and fill the function down column F to return the bonus for each qualifying submission.
- In cell I4, use two SUMIFS functions to find the Total Bonus amount for the consultant whose ID is given in cell H4. The first SUMIFS will add the Speed Bonus values for the consultant; the second SUMIFS will add the Negative Bonus values for the consultant. The two SUMIFS functions will need to be added together to find the Total Bonus for the consultant. Format the values as Currency, and fill your functions down column I to return the Total Bonus for each consultant.
- Note: 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.
Hint: You can check your work by comparing quick sums in Excel…
On the EmployeeRaises worksheet, calculate several different raise options for a sample set of employees with the rules given. Format all values as Accounting.
Note: A value of 0 in the Accounting format will display as a hyphen. This is okay.
- In Scenario 1, raises would go to employees who are at least 60 years old and who have worked for the company for at least 5 years. In cell H5, 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. Fill the function through cell H28 to calculate correct raises for each person that qualifies in Scenario 1.
- In Scenario 2, raises would go to employees who are either in the IT department or who have worked for the company for less than 3 years. In cell I5, 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. Fill the function through cell I28 to calculate correct raises for each person that qualifies in Scenario 2.
- In Scenario 3, raises would be awarded to all employees who have worked with the company for at least 5 years who make less than $50,000. In cell J5, write a function that would calculate the raise for employees who fit this description. If they qualify, then they get a $2,000 raise. All other employees get no raise. Fill the function through cell J28 to calculate the raise for each person based on this scenario.
- In the New Salaries column, list the effective new salary of all employees given any of these scenarios. In K5, write a formula to calculate this new salary using MAX and simple math, filling the formula down to K28. Note the comparative SUMs at the bottom of this table our managers can use.
The KitchenItems worksheet contains a list of transactions in which items from the Kitchen department were purchased from the furniture store we discussed earlier.
- 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.
- Over 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.
- Back 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.
- In cell L5, use a function to find the number of transactions that were at or above the average revenue.
- In cell L8, use a simple function to find the number of transactions given on this worksheet.
- 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.
You may now save and close your Excel workbook. Make sure the most recently worked file is in your IU OneDrive with the proper date modified timestamp.