Chapter 6 – Aggregates and Rounding
Introduction
In Chapter 2 we will examine how to perform a variety of calculations to derive aggregate data, which means we want to compile a series of numbers as sums or averages, for example.
- Use basic mathematical formulas to calculate percentages
- Use autofill to easily copy formulas and data from one cell to many others in a series
- Use the aggregate functions AVERAGE, MIN, MAX, and SUM
- Discuss and use ROUND and how that compares to various formatting options (e.g., Accounting format)
Guided Exercises
In your IU OneDrive, open the Chapter06_AggregatesAndRounding file.
A. Target Stores
Percentages_Target Worksheet
Next year, you might have an apartment and you will need some items to furnish it. You go to Target and purchase the items listed in A6:A10. Use formulas and cell references to complete each problem.
- In cell D6, enter a formula to find the cost of purchasing the item in cell A6. Fill your formula down to cell D10.
- In cell D11, use a function to find the Subtotal before any discounts or tax.
- You have a Target RedCard which means you save 5% on every purchase. In cell D12, calculate how much you will save to the nearest penny.
- In cell D13, find the new amount that you owe.
- In cell D14, calculate how much you will owe in Sales Tax using the tax rate given in cell H2. Round to the nearest penny.
- In cell D15, find how much you will owe.
- The data in cells A20:B28 is the quarter 1 revenue from various departments of the Bloomington Target store. In cell C20, use a formula to calculate each department’s percent of the total revenue from quarter 1. Round to 3 decimal places and format as a percent with 1 decimal place displayed.
You may use a SUM at the bottom of column B for your denominator.
B. Haptown Asset Markups and Commissions
Percentages_Haptown Worksheet
The data in cells A5:B13 represents assets that Haptown needs to replace this year alongside their initial purchase costs when they were obtained.
- Haptown estimates a 10% inflation rate indicated in cell E1. In cell C5, use a formula to calculate what the city can expect to pay now. Fill the formula down to cell C13.
- Haptown must also pay taxes on this type of asset since they may come from across state lines. The current tax rate is in cell E2. In cell D5, use a formula to calculate that tax amount based on the new cost and that tax rate. Round to the nearest penny and format as Accounting. Fill the formula down to cell D13.
- Note the sums at the right and the total amount Haptown will have to pay given these estimates.
C. University Students and Costs
UniversityNumbers Worksheet
This worksheet gives four pieces of data from some public universities in the United States.
- In cell H3, use a function to find the total number of full-time students.
- In cell H4, use a function to find the average number of full-time students enrolled in a public university. Round this value to the nearest whole number.
- In cell H5, use a function to find what the highest enrollment of full-time students was.
- In cell H6, use a function to find what the lowest enrollment of full-time students was.
- Format the out-of-state tuition and room & board costs as Accounting with 0 decimal places displayed.
- In cell K4, use a function to find the average room & board costs from these public universities. Round this value to 2 decimal places and display 2 decimal places.
- In cell K5, use a function to find the highest room & board costs.
- In cell K6, use a function to find the lowest room & board costs.
- If the values in K4:K6 did not automatically format as Accounting, format them as Accounting.
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
What you should work on before our next class
In your IU OneDrive, open the Chapter06_AggregatesAndRounding file.
You have a job interview, but you don’t have a suit to wear! Luckily, you have a 20% off coupon at Kohl’s this week. Use the NewSuit worksheet to figure out what the total spent is going to be.
- In cell D5, enter a formula to find the cost of purchasing the item in cell A5. Fill your formula down to cell D10.
- In cell D11, use a function to find the Subtotal before any discounts or tax.
- The discount percent is in cell G2. In cell D12, calculate how much you will save to the nearest penny. Make sure you reference cell G2 just in case the percent changes.
- In cell D13, find the new amount that you owe.
- In cell D14, calculate how much you will owe in Sales Tax using the tax rate given in cell G3. Round to the nearest penny.
- In cell D15, find how much you will owe.
Remember the student survey that you were asked to complete before classes started (or during the first week of classes)? Some of the data is on the StudentData worksheet. Use your Excel skills to perform the following tasks:
- Center the text in cell A1 across A1:D1. Change the font to 16 pt with a green fill color.
- In the gray filled cells in column F, use functions to answer the questions in column G. For problems that ask for an average, round the answers to 1 decimal place.
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.