PERS2002: Business and Technology

Dr. Satish Nargundkar

Assignment 5: Spreadsheet Analysis

1. On a worksheet, enter grades at random for 10 fictitious students. Observation numbers should be in column A, student First and Last names in columns B and C, and then the grades in the columns after that.

a. Each student should have points (out of 100) for three tests, two projects, and a final exam. The tests and projects all count 15% each for a total of 75%, and the final exam is weighted 25%.

b. Compute the course average for each student.

c. Use the Vlookup statement in Excel to figure out the letter grade for each student based on the course average. You will have to create a lookup table to translate averages to grades. Use the translation scheme from averages to grades as shown in our course syllabus. (94+ = A, 90 – 93 = A-, etc.)

2. Draw a Line chart showing the Final Exam scores on the y-axis and the Last Names on the X-axis. Make sure the data is sorted in descending order of scores.

3. Add two columns to identify the gender of each student and the major (use only 2 majors, “Business” and “Arts”, and assign them randomly to the 10 students). Create a pivot table to analyze the Final Exam scores by Gender and Major.

Save the Excel file and upload to iCollege.