You have been offered an internship in the School of Management and the Dean has asked you to fix problems with inconsistent data and grading criteria among the different sections of a GMU class over a period of several years. You need to validate, format, and calculate each student's data using the same criteria for Letter Grade and Pass/Fail. The maximum number of points in the course is 695 and the following table explains letter grades by number on points: Total Points Letter Grade Total Points Letter Grade
|
Total Points
|
Letter Grade
|
Total Points
|
Letter Grade
|
|
660
|
A+
|
500
|
C+
|
|
630
|
A
|
450
|
C
|
|
600
|
A-
|
420
|
C-
|
|
570
|
B+
|
390
|
D
|
|
545
|
B
|
0
|
F
|
|
520
|
B-
|
|
|
Your tasks are as follows - be sure to follow them explicitly:
1) Using data in the file Data.txt, create a file named: 'HW3' in either .xls or .xlsx format.
2) Copy and paste the raw data from the text file into the first worksheet and label it 'Student Scores'
3) Complete the following validation, formatting, and calculation steps:
a. Make sure totals are correct and column values are reasonable. Identify any usual values in accordance with our discussion and rubric. Format all point value cells to one decimal point.
b. Add a column labeled "Calculated Points" which sums all of the exams, projects and quizes. Format the calculated total to one decimal point.
c. Add an additional column that compares the calculated total value with the given total value. Identify if they match or not.
d. Add a column labeled 'Total %' that calculates a percentage score for each student and format it to display out to one decimal point.
e. Add a column labeled 'Letter Grade' and use VLOOKUP to automatically assign final letter grades based on the total calculated points column in the table above. Be sure to put your grade lookup data beginning at Cell A1 on a separate worksheet labeled, 'Lookup Data' (1 pt). If in step 3a you found any invalid values, manually replace the VLOOKUP grade value for that row with 'Invalid' to denote the data do not support calculating a grade value.
e. Add a column labeled 'Pass/Fail' and use the IF function to populate either 'Pass' or 'Fail' (Passing = C- or higher) (1 pt), then use Conditional Formatting to highlight all cells with 'Fail' with red fill (.5 pt) . Manually replace Pass/Fail values for rows that whose Letter Grade = 'Invalid' with 'N/A'.
Attachment:- Assignment.rar