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

15

Now that you entered the criteria, you want to calculate the average score.

In cell Q9, insert the DAVERAGE function. Use the range C1:N21 for the database argument, cell Q1 for the field argument, and the range P13:Q14 for the criteria argument.

5

16

Next, you want to count the number of times the scores within the criteria range occur.

In cell Q10, insert the DCOUNT function. Use the same arguments that you used for the DAVERAGE function. Change cell Q1 to
October. The results of the LARGE, DAVERAGE, and DCOUNT functions should change.

6

17

You will insert functions to look up data from the Scores worksheet. Your first task, however, is to assign range names to each client’s FICO scores.

Select the range B2:N21 in the Scores worksheet. Create range names from the selection using the left column.

5

18

You will create a summary section on the LookUp worksheet.

Display the LookUp worksheet. In cell A8, insert the TRANSPOSE function. Select the Scores worksheet and the range C1:N1 for the array of months.

5

19

Next, you want to display FICO scores based on the ID entered in cell B3, which corresponds to a range name.

In cell B8 on the LookUp worksheet, insert the TRANSPOSE function. Nest the INDIRECT function using cell B3 as its argument. The result is an array of 12 FICO scores for the client.

5

20

You want to create a line chart to depict the 12-month period of FICO scores for the client.

Select the range A7:B17 and insert a line chart. Paste the chart so that its upper-left corner is inside cell C7. For the chart title, type
=LookUp!$B$3 in the Formula Bar.

6

21

Cell B3 contains a customer ID, and cell B4 contains a month. Based on that data entry, you want to retrieve the FICO score.

In cell B5, insert the INDEX function. The array argument is range C2:N21 on the Scores sheet. The row_num argument contains a nested XMATCH function to look up the ID in cell B3 and compare it to the range B2:B21 in the Scores worksheet. The column_num argument contains a nested XMATCH function to look up the month in cell B4 and compare it to the range C1:N1 in the Scores worksheet.

4

22

You want to make sure the formulas and chart are correctly constructed.

In cell B3, type
D704H. In cell B4, type
May. Observe changes in the function results and in the line chart.

4

23

You are ready to document formulas on the LookUp worksheet.

In cell H2, insert the FORMULATEXT function to display the formula that is in cell B5.
In cell H3, insert the FORMULATEXT function to display the formula that is in cell A8.
In cell H4, insert the FORMULATEXT function to display the formula that is in cell B8.

9

24

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

0

Total Points

100