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!
368 Experts Online