Innovations Game
Studio has locations in Portland, Seattle, and Salt Lake City. Each location
has game-development teams to produce video games for various consoles. You
will use text functions to format the list and copy records of programmers in
one location. In addition, you will insert database functions to calculate
summary statistics and create a lookup area to look up an employee’s ID to
retrieve that person’s name, job title, and salary.
Step |
Instructions |
Points Possible |
1 |
Start Excel. Download and open the file
named Exp19_Excel_Ch11_ML2_GameStudio.xlsx.
Grader has automatically added your last name to the beginning of the
filename. |
0 |
2 |
You want to
combine the first, middle, and last names into one cell for each person. |
5 |
3 |
Column F contains job titles and
departments. You want to separate the data into two columns. |
6 |
4 |
You want to
display just the city names in column I by nesting a LEN function within a
LEFT function. |
5 |
5 |
The state abbreviations should be
formatted in uppercase letters. |
5 |
6 |
You want to
create a criteria range to enter data to filter the dataset based on
conditions. |
5 |
7 |
You are ready to create the output range
and perform the advanced filter. |
5 |
8 |
Hide columns B,
C, D, and H in the Salary Data worksheet. |
4 |
9 |
Change the width of column F to 21. |
0 |
10 |
You are now
ready to insert database functions on the Information sheet. |
5 |
11 |
In cell B3, insert the appropriate
database function to calculate the average salary for programmers in |
5 |
12 |
In cell B4,
insert the appropriate database function to identify the highest salary for
programmers in Salt |
5 |
13 |
In cell B5, insert the appropriate
database function to identify the lowest salary for programmers in Salt Lake |
5 |
14 |
In cell B6,
insert the appropriate database function to count the number of programmers
in Salt Lake City. |
5 |
15 |
You are ready to format the values. |
3 |
16 |
Format cell B6
with Comma Style with zero decimal places. |
2 |
17 |
You want to combine text using the CONCAT
function. |
5 |
18 |
Before using an
INDEX function, you want to use the MATCH function to identify the position
of an ID. |
4 |
19 |
In cell E4, insert the INDEX function
with Salary Data!A$2:K$49 as the
array, E$3
that contains the MATCH function as the row number, and 4 as the column number to
retrieve the last name corresponding to the ID in cell E3. |
4 |
20 |
Copy the INDEX
function to the range E5:E6. Edit the function in cell E5 by changing 4 to 7. Edit the function in cell
E6 by changing 4 to 11.
Format cell E6 with Accounting Number Format with zero decimal places. Change
the ID in cell E2 to 17604 to test the results of the MATCH and
INDEX functions. |
9 |
21 |
In cell H2, insert the function to
display the formula that is stored in cell B2. |
3 |
22 |
In cell H3,
insert the function to display the formula that is stored in cell E3. Copy
the function to cell H4. |
3 |
23 |
In cell H5, insert the function to
display the formula that is stored in cell B5. Copy the function to cell H6. |
3 |
24 |
Use the Home
tab to change the width for column H to 57. |
1 |
25 |
Create a footer with your name on the
left side, the sheet name code in the center, and the file |
3 |
26 |
Save and close Exp19_Excel_Ch11_ML2_GameStudio.xlsx.
Exit Excel. Submit the file as directed. |
0 |
Total Points |
100 |
Get Free Quote!
394 Experts Online