**Steps to complete This Project**

Mark the steps as checked when you complete them.

- Open the
start file. If the workbook opens in**ClassicGardens-06***Protected View*, click the**Enable Editing**button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. - Create a nested
*INDEX*and*XMATCH*function to display the number of transactions by city.

- Click the
**Transactions**sheet tab and select and name cells**A4:D29**as Stats. - Click the
**Transactions Stats**sheet tab. - Click cell
**B16**and type Springfield. - Click cell
**C16**, start an**INDEX**function, and select the first argument list option. - Choose or type the
**Stats**range name for the*Array*argument. - Click the
**Row_num**box and nest an**XMATCH**function. - Select cell
**B16**for the*Lookup_value*and cells**A4:A29**on the**Transactions**sheet for the*Lookup_array*. - Click
**INDEX**in the*Formula*bar to return to its*Function Arguments*dialog box and click the**Column_num**box. - Nest a second
**XMATCH**function to look up cell**C15**in the*lookup_array***A4:D4**on the**Transactions**sheet. - Click
**INDEX**in the*Formula*bar. The preview result in the*Function Arguments*dialog box is #N/A ().**Figure 6-113****Figure 6-113****Nested****XMATCH****and****INDEX****functions** - Click
**OK**.

- Click the
- Evaluate the formula.

- Select cell
**C16**and click the**Evaluate Formula**button [*Formulas*tab,*Formula Auditing*group]. - Click
**Evaluate**to work through each step of the formula. The error occurs at the fifth click, the second*XMATCH*function. - Close the dialog box.
- Click the
**Transactions**sheet tab and select cell**C4**. The label is**# Transactions**. - Click the
**Transactions Stats**sheet tab and select cell**C15**. - Edit the label to delete the word “of” and verify that there is one space between the # symbol and the word “Transactions.”
- Select cell
**C16**and format it as**Comma Style**with no decimal places. - Type Smyrna in cell
**B16**.

- Select cell
- Use
*DSUM*to summarize transaction and visit data.

- Click the
**Criteria**sheet tab. - Select cell
**B2**and type lan* to select data for the Landscape Design department. - Type law* in cell
**B5**for the Lawn & Maintenance department. - Select cell
**B8**and enter criteria for the Patio & Furniture department. - Select the
**Transactions**sheet and note that transactions are in the third column and visits are in the fourth column. - Click the
**Transactions Stats**sheet tab and select cell**B7**. - Use
**DSUM**from the*Database*category with the range name**Stats**as the*Database*argument. - 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. - Copy the formula in cell
**B7**to cell**C7**. - Edit the
*Field*argument in cell**C7**to use the fourth column. - Use
*DSUM*in cells**B8:C9**to calculate results for the two remaining departments.

- Click the
- Calculate totals and ratios.

- Use
*SUM*in cells**B10:C10**. - Select and format all values as
**Comma Style**with no decimal places. - Select cell
**D7**and enter a formula to divide cell**C7**by cell**B7**. - Format the results as
**Percent Style**with two decimal places. - Copy the formula to complete the column and select cell
**A5**().**Figure 6-114****Figure 6-114****Completed Transactions Stats sheet**

- Use
- Use
*SUMIFS*to total insurance claims and dependents by city and department.

- Click the
**Employee Insurance**sheet tab and select cell**E25**. - 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. - Build
*SUMIFS*formulas for cells**E26:E28**based on the criteria displayed in rows 26:28. - Format borders to remove inconsistencies, if any.

- Click the
- Use
*REPT*with*LEFT*and*CONCAT*to display names.

- Unhide column
**I**and select cell**B4**. - Start a
*CONCAT*function. The*Text1*argument is a nested*LEFT*function. - Select cell
**I4**as the**Text**argument for the*LEFT*function. - Click the
**Num_chars**box and type 3 to display the first three characters of the name. - Click
**CONCAT**in the*Formula*bar to return to its*Function Arguments*dialog box. - Click the
**Text1**argument box to expose the*Text2*argument box. - Nest the
**REPT**function from the*Text*category. - Type * as the
*Text*argument and repeat it**20**times. - Click
**CONCAT**in the*Formula*bar ().**Figure 6-115****Figure 6-115****Nested****Text****functions** - Click
**OK**. - Copy the formula and preserve the borders.
- Hide column
**I**and select cell**A1**.

- Unhide column
- Calculate depreciation for an asset using a
*Financial*function. Depreciation is the decrease in the value of an asset as it ages.

- Click the
**Depreciation**sheet tab and select cell**C11**. - Click the
**Financial**button [*Formulas*tab,*Function Library*group] and choose**DB**. The**DB**function calculates the loss in value over a specified period of time at a fixed rate. - Select cell
**C6**for the*Cost*argument, and press**F4**(**FN+F4**) to make the reference absolute. This is the initial cost of the dozer. - Click the
**Salvage**box, select cell**C7**, and press**F4**(**FN+F4**)**.**This is the expected value of the dozer at the end of its life. - Click the
**Life**box, select cell**C8**, and press**F4**(**FN+F4**). This is how long the dozer is expected to last. - Click the
**Period**box and select cell**B11**. The first formula calculates depreciation for the first year ().**Figure 6-116****Figure 6-116****DB****function to calculate depreciation** - Click
**OK**. The first-year depreciation is $31,800.00. - Copy the formula in cell
**C11**to cells**C12:C18**. Each year’s depreciation is less than the previous year’s. - Select cell
**C19**and use**AutoSum**. The total depreciation plus the salvage value is approximately equal to the original cost.

- Click the
- Save and close the workbook (
).**Figure 6-117****Figure 6-117****Excel 6-5 completed** - Upload and save your project file.
- Submit file for grading.