Chapter 4 – Conditional Aggregates

Introduction

There may be scenarios where we need to aggregate on data to find a max or min or sum, but given specific conditions or filtering criteria. This is where Conditional Aggregates come in handy.

We may also need to reference cells in a formula, but copy that formula down a column or across a row – in order for some of these cell references to continue to work in this other cells, we have to use mixed or absolute cell referencing.

  • Use COUNTIFS, SUMIFS, AVERAGEIFS
  • Use Mixed-Cell Referencing (vs Absolute or Relative referencing)
  • Solve a series of comparative problems using the above functions

Guided Exercises

In your IU OneDrive, open the Chapter04_ConditionalAggregates file. You may want to try to turn on AutoSave in the upper left corner. If it doesn’t load, that’s okay, Excel will prompt you to save when you close your file at the end.

ConditionalAggregatesNotes worksheet

This sheet summarizes the three conditional aggregate functions: COUNTIFS, SUMIFS, and AVERAGEIFS. Read the information on this worksheet.

The ItemAnalysis worksheet contains sales transaction data from a furniture wholesaler.

  1. In cell H4, write a formula to calculate the Revenue for the transaction in row 4. Fill the formula down column H to calculate the revenue for each transaction.
  2. In cell K4, enter a function to return the number of transactions that occurred in which items from the department in cell J4 were purchased. Fill the function down through cell K10 to find the number of transactions for each department.
  3. In cell L4, enter a function to return the total revenue from transactions in which items from the department in cell J4 were purchased. Format the total revenue as Accounting. Fill the function down through cell L10 to find the total revenue for each department.
  4. In cell M4, enter a function to return the average number of items sold for the department in cell J4. Round the value to the nearest whole number. Fill the function down through cell M10 to find the average number of items sold per transaction for each department.
  5. In cell J13, use a function to return the number of transactions on this worksheet.
  6. In cell J15, use a function to return the total number of products sold that are made from recycled materials.
  7. In cell J16, use a function to find the total revenue of items that are made from recycled materials. Format the value as Accounting.
  8. In cell J17, use a function to find the average volume for items that are made from recycled materials. Round the value to 1 decimal place.
  9. In cell J19, use a function to find how many transactions had a volume of 1000 or more items sold.
  10. In cell J20, use a function to find how many transactions had a volume of less than 100 items sold.
  11. In cell J21, use a function to find the number of transactions whose volume was between 200 and 400 items sold.
  12. In cell J23, use a function to find how many dining products are made from bamboo.
  13. In cell J24, use a function to find how many furniture items cost more than $500 each.
  14. In cell J25, use a function to find the total number of kids room beds that were sold.
  15. In cell J27, use a function to return the number of transactions that occurred on or after October 1, 2015.
  16. In cell J28, use a function to find the total revenue from Bedroom items that were sold in the month of August.
  17. In cell J30, use a function to find how many items with a price of at least $500 were sold altogether.
  18. In cell J31, use a function to find how many items with a price that is less than $100 were sold altogether.
  19. In cell J33, use a function to find the average number of Living room chairs sold per transaction. Round the value to 1 decimal place.

MixedCellReferencing worksheet

  1. In Scenario A, you want to figure out how much money you can make per week if you work a certain number of hours and make a certain wage per hour. There are various values for the hours you might work per week in cells B5:B13, and there are various values for the hourly wage you might make in cells C4:M4. To calculate how much you will make per week, write one formula in cell C5 that can be filled both across and down the table.
  2. In Scenario B, you want to calculate how many miles you can drive if you maintain one of the average speeds in cells B19:B27 and if you travel for any of the number of hours in cells C18:M18. Write one formula in cell C19 that can be filled both across and down the table to calculate the number of miles you’ll go.

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

What you should work on before our next class

In your IU OneDrive, open the Chapter04_ConditionalAggregates file. You may want to try to turn on AutoSave in the upper left corner. If it doesn’t load, that’s okay, Excel will prompt you to save when you close your file at the end.

Practice1

  1. You have developed a new product and are trying to start a small business to sell that product. You want to see how much Revenue you will bring in if you sell a certain number of items as indicated in cells B5:B19 and if you charge a certain price for your product as indicated in cells C4:Q4. Write one formula in cell C5 that can be filled both across and down the table to calculate the possible revenues. Adjust the column widths so that all values are visible.

Practice2

There is some data related to students’ genders, test scores, and how much money they raised for a fundraiser that the school was having.

  1. In cell F3, use a function to return how many students are in this dataset.
  2. In cell F5, use a function to return how many students earned at least 60 points on the test.
  3. In cell F6, use a function to return how many female students earned a perfect score of 100 points.
  4. In cell F8, use a function to calculate the total money that was raised.
  5. In cell F10, use a function to return the amount of money that was raised by the males. Format as Accounting.
  6. In cell F11, use a function to return the amount of money that was raised by the females who scored at least 80 on the test. Format as Accounting.
  7. In cell F12, use a function to find the total amount raised by all students who scored between 60 and 100 on the test. Format as Accounting.
  8. In cell F14, use a function to find the number of students who scored between 60 and 79 points on the test.
  9. In cell F15, use a function to find how many students raised between $20 and $50.
  10. In cell F17, use a function to find how many male students who earned a score of at least 60 raised at least $20?
  11. In cell F19, use a function to find the average score on the test. Round the value to 2 decimal places. Format the cell so that 2 decimal places are displayed.
  12. In cell F21, use a function to return the average score earned by the males who raised less than $50. Round the value to 2 decimal places.
  13. In cell F22, use a function to return the average score earned by the females. Round the value to 2 decimal places.

Practice3

This worksheet contains purchase data associated with the salespeople at a company. Use this data to complete the following tasks:

  1. In cell E5, write a formula to calculate the Revenue for the transaction in row 5 using the quantity of items purchased in cell B5 and the Price of the item in cell C5. The values should automatically format as Currency. Fill the formula down column E to calculate the Revenue for all transactions.
  2. In cell G5, the Shipping Fee should now get added to the Revenue to return the TotalCharge for the transaction. Fill this formula down column G so that each transaction’s TotalCharge is calculated.
  3. The table in cells I3:L25 will summarize each salesperson’s performance. In cell J5, use a function to return the number of transactions the employee in cell I5 had. Write your function so that it can be filled down through J25 and will return the number of transactions each salesperson had.
  4. In cell K5, use a function to return the total number of items (Volume) the salesperson in cell I5 sold in all his/her transactions. Write your function so that it can be filled down through K25 and will return the total number of items that each salesperson sold.
  5. In cell L5, use a function to return the average revenue that the salesperson in cell I5 brought in to the company. Write your function so that it can be filled down through L25 and will return the average revenue that each person made. You should not include the shipping fees in this calculation. Round the value to 2 decimal places. Format the values as Accounting.

 

License

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