1

Start Excel. Download and open the file named Exp22_Excel_Ch11_CumulativeAssessment_Variation_Accountants.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

You will use the Data worksheet through Step 14. Your first step is to create a unique ID for each accountant using the year hired, date hired, and number.

In cell D2, enter 2007-1018-10 and use Flash Fill to complete the pattern to create the remaining IDs in the range D3:D26.

5

3

The accountants’ full names are located in column E. You want to separate the first and last names.

Select the range E2:E26 and convert text to columns using the space as the delimiter.

8

4

Next, you want to display the accountants’ names with the last name and first name, separated by a comma.

In cell G2, insert the TEXTJOIN function to join the last name and first name for the first accountant, using a comma and space as the delimiter. The name should display as Adams, Camille. Copy the function to the range G3:G26.

6

5

Column J contains the phone numbers. You want to extract the area codes.

In cell K2, use the MID function to extract the area code for the phone number for the first employee. The Start_num argument should be the position of the first digit in the area code to avoid including the opening parenthesis. Make sure the function extracts the three-digit area code. Copy the function to the range K3:K26.

6

6

The Location column displays the city names in all capital letters. Because this is hard to read, you will use a text function to display the cities in upper and lowercase.

In cell M2, use the PROPER function to display the first city name in upper and lowercase. Copy the function to the range M3:M26.

5

7

You want to create a criteria range to perform an advanced filter. The criteria are (1) Tax Accountants in Miami and (2) Tax Accountants in Columbus.

Copy the range A1:M1 and paste it in the range A28:M28. Enter Tax Accountant and Miami in the respective cells on row 29. Enter Tax Accountant and Columbus in the respective cells on row 30. Use column M for the city names.

5

8

Now you are ready to perform the advanced filter by copying the data below the criteria range.

Click or select a cell within the dataset. Select A1:M26 as the list, enter the criteria range you defined in the previous step, and type A32:M32 for the location for the copied records.

5

9

You want to identify the highest salary for Tax Accountants in Miami and Columbus.

In cell P2, insert the DMAX function.

5

10

Next, you want to identify the lowest salary of Tax Accountants in Miami and Columbus.

In cell P3, insert the DMIN function.

5

11

The range O5:P6 contains a new set of criteria to identify the one Senior Accountant in Columbus. You want to obtain that person’s salary.

In cell P8, insert the DGET function to extract the correct salary.

6

12

The range O10:P13 is designed to look up a person’s name to return the salary for that person and the cell containing that salary. First, you will look up the person’s salary within the dataset.

In cell P12, insert an INDEX function with nested XMATCH functions. Type F2:I26 as the array. The first nested XMATCH function looks up the name entered in cell P11 and compares it to the range containing the last names in the dataset. The second XMATCH function looks up the text “Salary” in cell O12 and compares it to the range F1:I1. In cell P11, type Unice to test that the function returns the correct salary.

7

13

Now that you have retrieved the salary for the accountant, you are ready to identify the cell that contains that salary.

In cell P13, insert the ADDRESS function with nested XMATCH functions. The first XMATCH function looks up the name entered in cell P11 and compares it to the range F1:F26. The second XMATCH function looks up the text “Salary” in cell O12 and compares it to the range A1:M1. Enter the argument value to display the result as a relative cell address.

6

14

You want to document three formulas on the Data worksheet.

In cell P16, insert the FORMULATEXT function to display the formula that is stored in cell P8.
In cell P17, insert the FORMULATEXT function to display the formula that is stored in cell P12.
In cell P18, insert the FORMULATEXT function to display the formula that is stored in cell P13.

6

15

The Summary worksheet is designed to look up the average salary for a specific city. Each city has its own worksheet. On each city worksheet, cell F15 contains the average salary for the accountants in that city.

Display the Summary worksheet. In cell B3, insert the INDIRECT function that uses the city in cell B2 (which matches a sheet tab). Insert the symbols to point to cell F15. The function should retrieve the value in cell F15. In cell B2, type Columbus to test that the function result is correct.

7

16

You will use the Accountants worksheet for the remaining tasks. That worksheet contains a similar version of the dataset used on the Data worksheet. Your first task is to create a filtered list.

In cell H2, insert the FILTER function to filter the data in the dataset range A2:F26. The filter should evaluate if the data below the Title column heading equals Tax Accountant.

6

17

Next, you want to create an alphabetical list of the cities contained in the dataset.

In cell O2, insert a SORT function with a nested UNIQUE function for the range of cities contained in the dataset.

6

18

Finally, you want to list the top five salaries for tax accountants.

In cell O14, insert a LARGE function with a nested SEQUENCE function. Use the range of salaries from the filtered list and create a sequence of the top five salaries.

6

19

Save and close Exp22_Excel_Ch11_CumulativeAssessment_Variation_Accountants.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100