Exp22_Excel_Ch11_Cumulative – Client FICO Scores

Step

Instructions

Points Possible

1

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

0

2

You will use the Clients worksheet through Step 8. When you download data from the database server, the clients’ last names display in all capital letters. You want to make it easier to read the last names. In cell E2, insert the text function that displays the upper- and lowercase letters for the last names in the range D2:D21. Using the range in the function argument creates an array of last names so that you do not have to copy the function down the column.

5

3

Your next task is to join the first initial and last name in another column. In cell F2, insert the TEXTJOIN function that combines the first client’s initial in cell C2 with the person’s last name in cell E2. Use a period and space as the delimiter. Copy the function to the range F3:F21.

5

4

You want to create updated client IDs. In cell B2, insert the CONCAT function that combines the initial in cell C2, the old ID in cell A2, and the first letter of the last name in cell D2. Use a nested LEFT function to retrieve the first letter of the last name. Do not include any delimiters. Copy the function to the range B3:B21.

5

5

The City column contains city names and state abbreviations. You will separate the data into two columns. Select the range G2:G21 and convert it to columns. Use the comma as the delimiter and deselect other delimiter check boxes. Within the Wizard, make sure the two columns are formatted as Text.

3

6

When you converted text to columns, spaces remained to the left of the state abbreviations. You want to remove those spaces. Select the range H2:H21. Use Find and Replace to find a space and replace it with nothing.

2

7

Your next task is to create an array of the different locations where clients live. In cell J3, insert the UNIQUE function to list the unique city and state abbreviations in the range G2:H21. Nest the UNIQUE function within a SORT function to sort the results by the state abbreviation.

5

8

You want to count the number of unique rows. In cell K1, insert the ROWS function to count the number of unique locations. Nest the UNIQUE function within the ROWS function using the same array that you used in Step 7.

5

9

The Scores worksheet contains the client FICO scores for the entire year. First, you want to create an array of row numbers for the clients. Display the Scores worksheet. In cell A2, insert a ROW function that uses the range A2:A21 as the array. The results should then subtract the row number for A$1.

5

10

You are ready to create the criteria range so that you can perform an advanced filter. Copy the range A1:N1 to cell A23 to create the column labels for the criteria range. In cell C24, type >=800. Copy the criteria to the range D24:N24.

3

11

Next, you will create the column headings for the output range. Copy the range A1:N1 to cell A26.

2

12

You are ready to perform the advanced filter. Perform the advanced filter by copying data to the output area. Use the appropriate ranges for list range, criteria range, and output range.

4

13

You created a summary area where you can enter the month and then display an array of the top four FICO scores for that month. In cell P3, insert the LARGE function. Nest the INDIRECT function in the array argument to refer to the contents in cell Q1. Cell Q1 contains the name of a month, which is also the range name for the respective month’s FICO scores. Nest the SEQUENCE function in the k argument of the LARGE function to create an array of the top four FICO scores for the respective month. Change cell Q1 to April and notice that the top 4 FICO scores for April are different.

5

14

You want to create the criteria for the FICO scores in the Very Good credit rating category. In cell P14, type >=740. In cell Q14, type <=799.

2