1. Create a list from the data in the k: drive folder “Project” under Raw Data & Source. Once you have the Raw Data & Source open, include a Note field. This is to be a computational field. Name your list Database. Use the table in the Raw Data & Source worksheet to create a VLOOKUP Table in the range BZ7:CA12. Use the VLOOKUP Function to calculate your answers. Name your sheet Grades and change the tab color to red.

2. Rename your second sheet “Advanced Filtering” and change its tab color to blue. For the next three questions, extract your records from the grades sheet and use the advanced filter to copy the records to your “Advanced Filtering” worksheet. Come up with the criteria range and answer for each of the following questions. (A) Extract all records where the Percent (Grade) is greater than 80% and homework 6 is greater than 20. (B) Extract all records where Total Points are greater than 1850 and Note says Nice. (C) Extract all records that have the last name starting with 'R' or 'S' and a percent grade greater than 85%.

3. Rename your third sheet “D-Functions” and change its tab color to green. Come up with a criteria range and answer by using the correct D-Function to the following questions. (A) What is the average of homework # 72 with a percent grade greater than a 70% and less than a 90%? (B) How many students received either less than a 75% or greater than a 95% in the class? (C) How many students are there with the last name starting with 'S' and 'N'? (D) What is the highest grade in the class with a grade less than 85% on homework # 72? (E) What is the lowest amount of total points with a grade greater than a 70%? (F) What is the sum of total points for students whose last name starts with 'A'?

4. Insert a new worksheet and rename it “List Sorting”. Change its tab color to yellow. After each sort, copy and paste your answer on to your “List Sorting” worksheet. Then, make sure that you go back to Data/ Filter/ Show All. Complete the following three sorts. (A) Sort the list by percent grade within student's last name beginning with S. The percent grade is to be in descending order. (B) Sort the list by percent grade less than 70%. The sort key is to be in descending order. (C) Sort the list by homework # 72 greater than 90%. The sort key is to be in descending order.

5. Make a chart on the “Grades” worksheet that represents the students to their percent grade. Once you’re done with the chart, add some word art and format each of your pages to look beautiful. Save your workbook as “FINAL PROJECT” and quit excel.

6. Open the list you just created and insert another worksheet named “Goal Seek” with a tab color of purple. Use Goal Seek to figure out how many points Shannon Brown needs to get, if her goal is to receive a 70% in the class? Copy your answer and parameters to your “Goal Seek” worksheet. Insert a comment somewhere in your worksheet. Format the sheet.

7. Insert a new worksheet named “IF Function” and change its tab color to bright green. Add a new field to your “Grades” worksheet called Extra Credit. Define the name of your list so that extra credit is included. In your new field, use the IF Function to show if a student has a 90% or greater in the class, they receive 2% extra credit. Insert a comment somewhere on your worksheet.  Format the sheet. Save your workbook as FINAL PROJECT (ADVANCED CONCEPTS).