Independent Project 6-5

Classic Gardens and Landscapes counts transactions and installation visits to build a company profile. You use Database and Statistical functions to complete a summary. You also calculate insurance statistics and display masked last names in one cell.

[Student Learning Outcomes 6.1, 6.3, 6.5, 6.6, 6.7, 6.8]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

File Needed: ClassicGardens-06.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 6-5.xlsx

Skills Covered in This Project

• Nest XMATCH and INDEX functions.
• Evaluate a formula to find the error.
• Create DSUM formulas.
• Build SUMIFS formulas.
• Use CONCAT, LEFT, and REPT to mask names.
• Calculate depreciation using a Financial function.

1. Open the ClassicGardens-06 workbook from your student data files and save it as [your initials] Excel 6-5.
2. Create a nested INDEX and XMATCH function to display the number of transactions by city.
   1. Click the Transactions sheet tab and select and name cells A4:D29 as Stats.
   2. Click the Transactions Stats sheet tab.
   3. Click cell B16 and type Springfield.
   4. Click cell C16, start an INDEX function, and select the first argument list option.
   5. Choose or type the Stats range name for the Array argument.
   6. Click the Row_num box and nest an XMATCH function.
   7. Select cell B16 for the Lookup_value and cells A4:A29 on the Transactions sheet for the Lookup_array.
   8. Click INDEX in the Formula bar to return to its Function Arguments dialog box and click the Column_num box.
   9. Nest a second XMATCH function to look up cell C15 in the lookup_array A4:D4 on the Transactions sheet.
   10. Click INDEX in the Formula bar. The preview result in the Function Arguments dialog box is #N/A (Figure 6-113).
   11. Click OK.
3. Evaluate the formula.
   1. Select cell C16 and click the Evaluate Formula button [Formulas tab, Formula Auditing group].
   2. Click Evaluate to work through each step of the formula. The error occurs at the fifth click, the second XMATCH function.
   3. Close the dialog box.
   4. Click the Transactions sheet tab and select cell C4. The label is # Transactions.
   5. Click the Transactions Stats sheet tab and select cell C15.
   6. Edit the label to delete the word “of” and verify that there is one space between the # symbol and the word “Transactions.”
   7. Select cell C16 and format it as Comma Style with no decimal places.
   8. Type Smyrna in cell B16.
4. Use DSUM to summarize transaction and visit data.
   1. Click the Criteria sheet tab.
   2. Select cell B2 and type lan* to select data for the Landscape Design department.
   3. Type law* in cell B5 for the Lawn & Maintenance department.
   4. Select cell B8 and enter criteria for the Patio & Furniture department.
   5. Select the Transactions sheet and note that transactions are in the third column and visits are in the fourth column.
   6. Click the Transactions Stats sheet tab and select cell B7.
   7. Use DSUM from the Database category with the range name Stats as the Database argument.
   8. Type 3 for the Field argument (“# Transactions” column), and enter an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria argument. The result is 11555.
   9. Copy the formula in cell B7 to cell C7.
   10. Edit the Field argument in cell C7 to use the fourth column.
   11. Use DSUM in cells B8:C9 to calculate results for the two remaining departments.
5. Calculate totals and ratios.
   1. Use SUM in cells B10:C10.
   2. Select and format all values as Comma Style with no decimal places.
   3. Select cell D7 and enter a formula to divide cell C7 by cell B7.
   4. Format the results as Percent Style with two decimal places.
   5. Copy the formula to complete the column and select cell A5 (Figure 6-114).
6. Use SUMIFS to total insurance claims and dependents by city and department.
   1. Click the Employee Insurance sheet tab and select cell E25.
   2. Use SUMIFS with an absolute reference to cells E4:E23 as the Sum_range argument.
      • The Criteria_range1 argument is an absolute reference to cells D4:D23. The Criteria1 argument is bre* for the city of Brentwood.
      • The Criteria_range2 argument is an absolute reference to cells C4:C23, the department column, with criteria of lan* for the Landscape Design department.
      • Click OK. The result for cell E25 is 10.
   3. Build SUMIFS formulas for cells E26:E28 based on the criteria displayed in rows 26:28.
   4. Format borders to remove inconsistencies, if any.