Grader - Instructions Excel 2019 Project Step Instructions Points Possible 9 Change the width of column F to 21. 0 10 You are now ready to insert database functions on the Information sheet. 5 In cell B2, insert the appropriate database function to calculate the total salary for programmers in Salt Lake City. Use the range A$1 K$49 in the Salary Data worksheet for the database, Salary for the field, and the criteria range. 11 5 In cell B3, insert the appropriate database function to calculate the average salary for programmers in Salt Lake City 12 5 In cell B4, insert the appropriate database function to identify the highest salary for programmers in Salt Lake City 13 5 In cell B5, insert the appropriate database function to identify the lowest salary for programmers in Salt Lake City 14 5 In cell B6, insert the appropriate database function to count the number of programmers in Salt Lake City 15 You are ready to format the values. 3 Format the range B2:35 with Accounting Number Format with zero decimal places. 16 Format cell B6 with Comma Style with zero decimal places. 2 17 You want to combine text using the CONCAT function. 5 In cell B1, insert a CONCAT function that concatenates the text in 'Salary Data'lE52 and "Salary Data'!152 separated by a space, the word in, and another space. The result should look like this: Programming in Salt Lake City. 18 4 Before using an INDEX function, you want to use the MATCH function to identify the position of an ID. In cell E3, insert the MATCH function to identify the position of the ID stored in cell E2. Use the range A2:A49 in the Salary Data worksheet for the lookur array argument and look for exact matches only. 19 4 4 In cell E4, insert the INDEX function with Salary DatalA$2:$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. 20 9 Copy the INDEX function to the range E5:56. 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. 21 In cell H2, insert the function to display the formula that is stored in cell B2. 3 22 3 In cell H3, insert the function to display the formula that is stored in cell E3. Copy the function to cell H4. Created On: 10/16/2019 2 Exp19_Excel_Ch11_ML2 - Game Studio 1.0 Grader - Instructions Excel 2019 Project Step Instructions Points Possible 9 Change the width of column F to 21. 0 10 You are now ready to insert database functions on the Information sheet. 5 In cell B2, insert the appropriate database function to calculate the total salary for programmers in Salt Lake City. Use the range A$1 K$49 in the Salary Data worksheet for the database, Salary for the field, and the criteria range. 11 5 In cell B3, insert the appropriate database function to calculate the average salary for programmers in Salt Lake City 12 5 In cell B4, insert the appropriate database function to identify the highest salary for programmers in Salt Lake City 13 5 In cell B5, insert the appropriate database function to identify the lowest salary for programmers in Salt Lake City 14 5 In cell B6, insert the appropriate database function to count the number of programmers in Salt Lake City 15 You are ready to format the values. 3 Format the range B2:35 with Accounting Number Format with zero decimal places. 16 Format cell B6 with Comma Style with zero decimal places. 2 17 You want to combine text using the CONCAT function. 5 In cell B1, insert a CONCAT function that concatenates the text in 'Salary Data'lE52 and "Salary Data'!152 separated by a space, the word in, and another space. The result should look like this: Programming in Salt Lake City. 18 4 Before using an INDEX function, you want to use the MATCH function to identify the position of an ID. In cell E3, insert the MATCH function to identify the position of the ID stored in cell E2. Use the range A2:A49 in the Salary Data worksheet for the lookur array argument and look for exact matches only. 19 4 4 In cell E4, insert the INDEX function with Salary DatalA$2:$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. 20 9 Copy the INDEX function to the range E5:56. 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. 21 In cell H2, insert the function to display the formula that is stored in cell B2. 3 22 3 In cell H3, insert the function to display the formula that is stored in cell E3. Copy the function to cell H4. Created On: 10/16/2019 2 Exp19_Excel_Ch11_ML2 - Game Studio 1.0
