Exp19_Excel_Ch11_ML1_Internships

Mamo_

Exp19_Excel_Ch11_ML1_Internships

.zip
  
Exp19_Excel_Ch11_ML1_Internships

Exp19 Excel Ch11 ML1 Internships

 Excel Chapter 11 Mid-Level 1 – Internships 

  

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. 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.

     

Start Excel. Download   and open the file named Exp19_Excel_Ch11_ML1_Internships.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 

You want to extract   the last four digits of the student’s ID.
 

  In cell B2 on the Students sheet, extract the last four digits of the first   student’s ID using the RIGHT function. Copy the function from cell B2 to the   range B3:B42.

 

After extracting the   last four digits of the ID, you want to align the data.
 

  Apply center horizontal alignment to the range B2:B42.

 

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.

 

You want to convert   the text in column F to upper and lowercase letters.
 

  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.

 

Now that you have   converted text from uppercase to upper and lowercase, you will hide the   column containing the majors in all capital letters.
 

  Hide column F.

 

You want to create a   criteria range for the dataset.
 

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

 

You are ready to   perform the advanced filter.
 

  Create an output range by copying 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

 

On the Info   worksheet, you want to insert a database function based on conditions.
 

  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.

 

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. Use mixed references in   the ranges.

 

You want to identify   the highest GPAs for junior and senior accounting majors.
 

  In cell B4, insert the DMAX function to identify the highest GPA for junior and   senior accounting students on the Students worksheet. Use mixed references in   the ranges.

 

In 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.

 

In 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.

 

In 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.

 

You want to format   the results of the database functions.
 

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

 

You want to identify   the location of a particular ID.
 

  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.

 

Insert the INDEX   function in cell B14 with Students!A$2:I$42 as the array, B$13 that contains
  the MATCH function as the row number, and 4 as the column number. Copy the   function from cell B14 to cell B15. Edit the function to change the column   number to 7.

 

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

 

You want to insert a   function to display other functions as text.
 

  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.

 

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.

 

Save and close Exp19_Excel_Ch11_ML1_Internships.xlsx.   Exit Excel. Submit the file as directed.

Mamo_Exp19_Excel_Ch11_ML1_Internships.xlsx
Info

Summary Statistics Formulas

Total Tuition

Average GPA

Highest GPA

Lowest GPA

Number of Students

Student ID Last Name GPA

44815181

Look Up

Student ID 75036772

Position

Last Name

Major

Students

Student ID Last 4 Name Last Name Status Major Major Update GPA Tuition

97052599 Ryan Griffith Junior CHEMISTRY 3.25 $4,000

75036772 Lisa Sanders Sophomore ENGLISH 2.00 $3,500

69867418 Roger Oliver Junior BIOLOGY 4.00 $4,000

88441289 Stanley Vasquez Freshman HISTORY 2.25 $3,000

60129576 Jeanne Fields Sophomore ENGLISH 2.75 $3,500

11720383 Emma Vega Sophomore PRE-LAW 4.00 $3,500

44815181 Judy Townsend Junior ECONOMICS 3.75 $4,000

11282378 Tonya Anderson Senior ACCOUNTING 3.95 $4,500

66076816 Craig Thompson Junior ACCOUNTING 3.00 $3,000

43501696 Edna Garcia Senior HISTORY 3.00 $4,500

55128275 Paul Rowe Senior MARKETING 3.85 $4,500

50305930 Christina Schmidt Senior ENGLISH 2.25 $4,500

92812678 Timothy Moss Senior FINANCE 4.00 $4,500

48507740 Kevin Wood Junior CHEMISTRY 3.75 $4,000

34844410 Rita Patterson Freshman ECONOMICS 2.00 $3,000

48681350 Edith Powell Junior COMMUNICATIONS 3.50 $4,000

48161142 Willie Reynolds Junior ENGLISH 4.00 $4,000

89046660 Rodney Scott Sophomore MARKETING 4.00 $3,500

33864244 Earl Reese Sophomore COMMUNICATIONS 3.15 $3,500

43691928 Veronica Duncan Senior HISTORY 2.00 $4,500

70219463 Angela Burns Senior MARKETING 3.90 $4,500

53836243 June Haynes Sophomore BIOLOGY 2.21 $3,500

21212682 Dana Spencer Junior CHEMISTRY 3.00 $4,000

23082780 Willie James Junior BIOLOGY 3.00 $3,000

55854472 Jesse Cox Senior PRE-LAW 3.75 $3,000

99045951 Yolanda Santiago Freshman COMMUNICATIONS 3.00 $3,000

64696712 Angela Mcgee Junior HISTORY 2.95 $4,000

84455099 Erica Silva Sophomore BIOLOGY 3.10 $3,500

12787204 Debra Riley Sophomore BIOLOGY 3.75 $3,500

47305021 Julie Oliver Senior ACCOUNTING 3.90 $4,500

18068129 Diana Williams Junior ENGLISH 3.45 $3,000

90525704 Stephanie Hawkins Sophomore BIOLOGY 3.45 $3,500

62496306 Jennifer Carroll Junior ACCOUNTING 2.75 $3,500

92975950 Peter Neal Freshman MARKETING 3.00 $3,000

22095477 Bradley Cox Junior CHEMISTRY 2.50 $4,000

59309642 Jesus Ferguson Senior COMMUNICATIONS 4.00 $3,000

60188653 Carolyn McDaniel Freshman BIOLOGY 3.00 $3,000

35430187 Fred Sharp Freshman PRE-LAW 4.00 $3,000

66228275 Rhonda Patton Sophomore PRE-LAW 4.00 $3,500

36141283 Bernard Horton Sophomore MARKETING 3.25 $3,500

93036307 Joel Mullins Freshman BIOLOGY 4.00 $3,000

Exp19_Excel_Ch11_ML1_Internships_Instructions x
Grader – Instructions Excel 2019 Project

Exp19_Excel_Ch11_ML1_Internships

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. 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.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp19_Excel_Ch11_ML1_Internships.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

You want to extract the last four digits of the student’s ID.
In cell B2 on the Students sheet, extract the last four digits of the first student’s ID using the RIGHT function. Copy the function from cell B2 to the range B3:B42.

5

3

After extracting the last four digits of the ID, you want to align the data.
Apply center horizontal alignment to the range B2:B42.

2

4

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.

6

5

You want to convert the text in column F to upper and lowercase letters.
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.

5

6

Now that you have converted text from uppercase to upper and lowercase, you will hide the column containing the majors in all capital letters.
Hide column F.

3

7

You want to create a criteria range for the dataset.
Create a criteria range by copying the range A1:I1 and pasting it in cell A44. Create conditions
for Senior Accounting majors on row 45 and an OR condition for Junior Accounting majors in the respective cells on row 46

10

8

You are ready to perform the advanced filter.
Create an output range by copying 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

10

9

On the Info worksheet, you want to insert a database function based on conditions.
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.

5

10

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. Use mixed references in the ranges.

5

11

You want to identify the highest GPAs for junior and senior accounting majors.
In cell B4, insert the DMAX function to identify the highest GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges.

5

12

In 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.

5

13

In 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.

5

14

In 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.

5

15

You want to format the results of the database functions.
Format the range B3:B6 with Comma Style. Decrease the number of decimal places to zero
for cell B6.

6

16

You want to identify the location of a particular ID.
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.

5

17

Insert the INDEX function in cell B14 with Students!A$2:I$42 as the array, B$13 that contains
the MATCH function as the row number, and 4 as the column number. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.

5

18

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

2

19

You want to insert a function to display other functions as text.
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.

5

20

Increase the width of column D to 50.

3

21

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.

3

22

Save and close Exp19_Excel_Ch11_ML1_Internships.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100

Created On: 10/15/2019 1 Exp19_Excel_Ch11_ML1 – Internships 1.0

Exp19_Excel_Ch11_ML1_Internship_final_result