Excel Question

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

   the following questions (put your answers and an explanation of how you got your answer to these questions in the “Answers” worksheet):

   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