### Excel Question

I have attached all the files needed to do this assignment.

The following is the assignment:

1. (20 points) In the worksheet named contacts, you will need to import the contacts from the text file “contacts.txt” included with the workbook using the following rules:

1. (16points) Each data item should be in its own column and you will need to give each column a heading including:

1. (6 points) Name: name should be arranged as “Last Name, First Name”

2. (2 points) Street: street address should be in its own column

3. (4 points) City: city should be in its own column

4. (4 points) State: state should be in its own column

2. (5 points) Sort the contacts by state, then name

2. (10 points) In the worksheet named “GR”, import ONLY the graduate students from the Access DB named Homework3.accdb

a. (5 points) Sort the students by last name, then first name, then GPA

1. (20 points) In the worksheet named “Students”, create a table using the data there to answer

1. How many students does Phillip Wisecup advise who major in Math?

2. How many students from the Ingram family (last name Ingram) are Sophmores in

Computer Science who have a GPA lower than 2.0?

3. What is the average GPA of all Freshman students being advised by Ken Mihavics?

4. What is the average GPA of all students in Theatre and Psychology?

2. (15 points) In the worksheet named “Employees”, perform the following date calculations:

1. (3 points) The birthday of each employee is shown in column F, calculate the age in

years for each employee in column G

2. (12 points) The start date of each employee is shown in column I, calculate the days,

months, and years until their ten-year anniversary in columns J, K, and L respectively

3. NOTE: You cannot use the DATEDIFF functions!

3. (35 points) Using the worksheet named “Students2”, create the following PivotTables (each in their own Worksheet):

1. (5 points) Create a PivotTable which organizes the data to show a count of the students in each class by Advisors (Note the advisors should be the columns)

2. (5 points) Create a PivotTable which organizes the data to show the average GPA of the students in classes by Advisors (Note the advisors should be the columns)

3. (5 points) Create a PivotTable which organizes the data to show the average GPA of the students by Advisors broken down by Majors and by Class

4. (10 points) Create a PivotTable which organizes the data to show the average GPA of the Majors by Class but only show the Freshman

5. (10 points) Create a PivotTable which organizes the data to show the average GPA of the Majors by Class (Note the class should be the columns) but only show the upperclassmen (Juniors and Seniors) from the students with family names starting with the letter B

##### ↑

For order inquiries        1-800-700-6200

Hi there! Click one of our representatives below and we will get back to you as soon as possible.

Chat with us on WhatsApp