Exp22_Excel_Ch11_CumulatveAssessment_Variation – Accountants

Exp22 Excel Ch11 CumulatveAssessment Variation Accountants

Excel Chapter 11 Cumulatve Assessment – Variation Accountants

Project Description:

You work for a tax accounting firm that has offices in Albany, Columbus, Dallas, and Miami. Previously, you downloaded data from the employee database. Now you will use text, database, array, and lookup functions to obtain the results you want.

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.

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. 

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.

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. 

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. 

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. 

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.

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.

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

In cell P2, insert the   DMAX function. 

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

  In cell P3, insert the DMIN function.

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.

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.

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.

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.

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.

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.

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.

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.

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