As the Internship Director for a regional university, you created a list of students who are currently in this semester’s internship program. You have some final touches to complete the worksheet, particularly in formatting text. In addition, you want to create an advanced filter to copy a list of senior accounting students. Finally, you want to insert summary statistics and create an input area to look up a student by ID to display his or her name and major.

a. Open e11m1Internships and save it as e11m1Internships_LastFirst.

b. Extract the last four digits of the first student’s ID in cell B2 using the RIGHT function. Copy the function from cell B2 to the range B3:B42. Apply center horizontal alignment to the range B2:B42.

c. Convert the text in the range C2:C42 into two columns using a space as the delimiter.

d. Use a text function in cell G2 to convert the text in cell F2 into upper and lowercase letters. Copy the function to the range G3:G42. Hide column F.

e. Create a criteria range by copying the range A1:I1 and pasting it in cell A44. Create condi-tions for Senior Accounting majors on row 45 and an OR condition for Junior Accounting majors in the respective cells on row 46.

f. Create an output range by copying the range A44:I44 to cell A48.

g. Perform the advanced filter by copying data to the output range. Use the appropriate ranges for list range, criteria range, and output range.

h. Display the Info worksheet and insert the DSUM function in cell B2 to calculate the total tuition for junior and senior accounting students. Use the range A1:I42 for the database, Tuition for the field, and the criteria range.

i. Insert database functions by completing these steps:

• Cell B3: Insert the DAVERAGE function to calculate the average GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B4: Insert the DMAX function to identify the highest GPA for junior and senior account-ing students on the Students worksheet. Use mixed references in the ranges. • Cell B5: Insert the DMIN function to identify the lowest GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B6: Insert the DCOUNT function to count the number of junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B9: Insert the DGET function to retrieve the last name of the student who has the ID listed in cell A9. Use the column number representing the Last Name column for the field argument and use the criteria range A8:A9. Edit the function to make the column letters absolute. • Copy the DGET function from cell B9 to cell C9. Edit the field number to represent the GPA column.

j. Format the range B3:B6 with Comma Style. Decrease the number of decimal places to zero for cell B6.

k. Insert the MATCH function in cell B13 to identify the position of the ID stored in cell B12. Use the range A2:A42 in the Students worksheet as the lookup_array argument and look for exact matches only.

l. Insert the INDEX function in cell B14 with Students!A$2:I$42 as the array, B$13 that con-tains the MATCH function as the row number, and 4 as the column number.

m. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.

n. Change the ID in cell B12 to 11282378. The results of the MATCH and INDEX functions should change.

o. Insert the FORMULATEXT function in cell D2 to display the formula that is stored in cell B2. Copy the function to the range D3:D6 and to the range D13:D15. In cell D8, insert the FORMULATEXT function to display the function that is stored in cell B9, and in cell D9, insert the FORMULATEXT function to display the function that is stored in cell C9. Increase the width of column D to 50

p. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.

q. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e11m1Internships_LastFirst.

As the Internship Director for a regional university, you created a list of students who are currently in this semester’s internship program. You have some final touches to complete the worksheet, particularly in formatting text. In addition, you want to create an advanced filter to copy a list of senior accounting students. Finally, you want to insert summary statistics and create an input area to look up a student by ID to display his or her name and major.

a. Open e11m1Internships and save it as e11m1Internships_LastFirst.

b. Extract the last four digits of the first student’s ID in cell B2 using the RIGHT function. Copy the function from cell B2 to the range B3:B42. Apply center horizontal alignment to the range B2:B42.

c. Convert the text in the range C2:C42 into two columns using a space as the delimiter.

d. Use a text function in cell G2 to convert the text in cell F2 into upper and lowercase letters. Copy the function to the range G3:G42. Hide column F.

e. Create a criteria range by copying the range A1:I1 and pasting it in cell A44. Create condi-tions for Senior Accounting majors on row 45 and an OR condition for Junior Accounting majors in the respective cells on row 46.

f. Create an output range by copying the range A44:I44 to cell A48.

g. Perform the advanced filter by copying data to the output range. Use the appropriate ranges for list range, criteria range, and output range.

h. Display the Info worksheet and insert the DSUM function in cell B2 to calculate the total tuition for junior and senior accounting students. Use the range A1:I42 for the database, Tuition for the field, and the criteria range.

i. Insert database functions by completing these steps:

• Cell B3: Insert the DAVERAGE function to calculate the average GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B4: Insert the DMAX function to identify the highest GPA for junior and senior account-ing students on the Students worksheet. Use mixed references in the ranges. • Cell B5: Insert the DMIN function to identify the lowest GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B6: Insert the DCOUNT function to count the number of junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B9: Insert the DGET function to retrieve the last name of the student who has the ID listed in cell A9. Use the column number representing the Last Name column for the field argument and use the criteria range A8:A9. Edit the function to make the column letters absolute. • Copy the DGET function from cell B9 to cell C9. Edit the field number to represent the GPA column.

j. Format the range B3:B6 with Comma Style. Decrease the number of decimal places to zero for cell B6.

k. Insert the MATCH function in cell B13 to identify the position of the ID stored in cell B12. Use the range A2:A42 in the Students worksheet as the lookup_array argument and look for exact matches only.

l. Insert the INDEX function in cell B14 with Students!A$2:I$42 as the array, B$13 that con-tains the MATCH function as the row number, and 4 as the column number.

m. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.

n. Change the ID in cell B12 to 11282378. The results of the MATCH and INDEX functions should change.

o. Insert the FORMULATEXT function in cell D2 to display the formula that is stored in cell B2. Copy the function to the range D3:D6 and to the range D13:D15. In cell D8, insert the FORMULATEXT function to display the function that is stored in cell B9, and in cell D9, insert the FORMULATEXT function to display the function that is stored in cell C9. Increase the width of column D to 50

p. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.

q. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e11m1Internships_LastFirst.

As the Internship Director for a regional university, you created a list of students who are currently in this semester’s internship program. You have some final touches to complete the worksheet, particularly in formatting text. In addition, you want to create an advanced filter to copy a list of senior accounting students. Finally, you want to insert summary statistics and create an input area to look up a student by ID to display his or her name and major.

a. Open e11m1Internships and save it as e11m1Internships_LastFirst.

b. Extract the last four digits of the first student’s ID in cell B2 using the RIGHT function. Copy the function from cell B2 to the range B3:B42. Apply center horizontal alignment to the range B2:B42.

c. Convert the text in the range C2:C42 into two columns using a space as the delimiter.

d. Use a text function in cell G2 to convert the text in cell F2 into upper and lowercase letters. Copy the function to the range G3:G42. Hide column F.

e. Create a criteria range by copying the range A1:I1 and pasting it in cell A44. Create condi-tions for Senior Accounting majors on row 45 and an OR condition for Junior Accounting majors in the respective cells on row 46.

f. Create an output range by copying the range A44:I44 to cell A48.

g. Perform the advanced filter by copying data to the output range. Use the appropriate ranges for list range, criteria range, and output range.

h. Display the Info worksheet and insert the DSUM function in cell B2 to calculate the total tuition for junior and senior accounting students. Use the range A1:I42 for the database, Tuition for the field, and the criteria range.

i. Insert database functions by completing these steps:

• Cell B3: Insert the DAVERAGE function to calculate the average GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B4: Insert the DMAX function to identify the highest GPA for junior and senior account-ing students on the Students worksheet. Use mixed references in the ranges. • Cell B5: Insert the DMIN function to identify the lowest GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B6: Insert the DCOUNT function to count the number of junior and senior accounting students on the Students worksheet. Use mixed references in the ranges. • Cell B9: Insert the DGET function to retrieve the last name of the student who has the ID listed in cell A9. Use the column number representing the Last Name column for the field argument and use the criteria range A8:A9. Edit the function to make the column letters absolute. • Copy the DGET function from cell B9 to cell C9. Edit the field number to represent the GPA column.

j. Format the range B3:B6 with Comma Style. Decrease the number of decimal places to zero for cell B6.

k. Insert the MATCH function in cell B13 to identify the position of the ID stored in cell B12. Use the range A2:A42 in the Students worksheet as the lookup_array argument and look for exact matches only.

l. Insert the INDEX function in cell B14 with Students!A$2:I$42 as the array, B$13 that con-tains the MATCH function as the row number, and 4 as the column number.

m. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.

n. Change the ID in cell B12 to 11282378. The results of the MATCH and INDEX functions should change.

o. Insert the FORMULATEXT function in cell D2 to display the formula that is stored in cell B2. Copy the function to the range D3:D6 and to the range D13:D15. In cell D8, insert the FORMULATEXT function to display the function that is stored in cell B9, and in cell D9, insert the FORMULATEXT function to display the function that is stored in cell C9. Increase the width of column D to 50

p. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.

q. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e11m1Internships_LastFirst.

a. Open e11m1Internships and save it as e11m1Internships_LastFirst.

a. Open e11m1Internships and save it as e11m1Internships_LastFirst.

c. Convert the text in the range C2:C42 into two columns using a space as the delimiter.

c. Convert the text in the range C2:C42 into two columns using a space as the delimiter.

f. Create an output range by copying the range A44:I44 to cell A48.

f. Create an output range by copying the range A44:I44 to cell A48.

i. Insert database functions by completing these steps:

i. Insert database functions by completing these steps:

m. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.

m. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.