Скачать или смотреть Exp22_Excel_Ch11_ML1_Internships | Exp22 Excel Ch11 ML1 Internships | @HomeWork Solution

Exp22_Excel_Ch11_ML1_Internships | Exp22 Excel Ch11 ML1 Internships | @HomeWork Solution

Contact us:

WhatsApp : +92 3706122896

WhatsApp Link:
http://bit.ly/4dto07N

Gmail : [email protected]

We can handle all online courses, Business and Management, Business and Finance, Business and Accounting, Statistics, Computer Science and Many more We will help you in weekly quizzes, assignments, exams,MylTLab all Courses

Project Description:
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


1 Start Excel. Download and open the file named Exp22_Excel_Ch11_ML1_Internships.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 You will use the Students worksheet through Step 8. First, you want to create an array of the last four digits of all student IDs.

In cell B2, create an array using the RIGHT function to extract the last four digits of the student IDs using the range A2:A42. Apply center horizontal alignment to the range B2:B42. 7
3 The first and last names are combined in column C. You want to separate the names into two columns.

Convert the text in the range C2:C42 into two columns using a space as the delimiter. Deselect other delimiters. 5
4 Column F contains each student's major in all capital letters. You want to display the majors in upper- and lowercase letters.

In cell G2, use a text function to convert the text in the range F2:F42 into upper and lowercase letters.


You want to create a criteria range for the dataset so that you can use this range in an advanced filter.

Copy the range A1:I1 and paste it in cell A44. Create conditions in the Major Update column for Senior Accounting majors on row 45 and an OR condition for Junior Accounting majors in the respective cells on row 46. 5
6 You are ready to perform the advanced filter.

Copy the range A44:I44 to cell A48. Perform the advanced filter by copying data to the output range. Use the appropriate ranges for list range, criteria range, and output range. 7
7 You will create a sorted list of majors.

In cell K3, nest the UNIQUE function within a SORT function to display an alphabetical list of each major from the range G2:G42. 5
8 Now you want to display a list of majors that occur only once.

In cell L3, insert the UNIQUE function to display a list of majors that occur only once within the range G2:G42. 5
9 You want to count the number of unique majors and then update one of the student's major. After you insert the function, you will change a student's major to ensure the function results are correct.

@HomWork Solution

In cell L1, nest the UNIQUE function within the ROWS function. Use the range containing the Major Update column data in the ROWS function. In cell F16, change the text to CULINARY ARTS. 7
10 On the Info worksheet, you want to insert a database function based on conditions.

Display the Info worksheet. In cell B2, insert the DSUM function to calculate the total tuition for junior and senior accounting students. Use the range A1:I42 from the Students worksheet for the database, Tuition for the field, and the criteria range on the Students worksheet. 5
11 You want to insert database functions to perform calculations.

In cell B3, insert the DAVERAGE function to calculate the average GPA for junior and senior accounting students on the Students worksheet. Format the range B3:B6 with Comma Style. 6
12 You want to identify the highest GPAs for junior and senior accounting majors.

@HomWork Solution

In cell B4, insert the DMAX function to identify the highest GPA for junior and senior accounting students on the Students worksheet. 5
13 In cell B5, insert the DMIN function to identify the lowest GPA for junior and senior accounting students on the Students worksheet. 5
14 In cell B6, insert the DCOUNT function to count the number of junior and senior accounting students on the Students worksheet. 5