Reference no: EM132275335
Gradebook Assignment
The objective of this assignment is to 1) recognize errors with data quality/integrity 2) practice skills learned in formatting and manipulating text strings, using formulas and functions, sorting data and descriptive data analysis
The assignment should be formatted in a professional manner, using appropriate titles and column headers, professional alignment and consistent formatting of all cells. Labels/titles should be used throughout your work so an uninformed reader can easily identify the information (including your name on each worksheet at top).
Save the file as an Excel file using the filename conventions specified earlier in the course. Submissions of the wrong file type will receive a grade of 0.
Scenario: The data file provided is sample student grade data from one class for one semester. The data is to be used to calculate final grades and analyze grade distributions.
Reformat the grade sheet so that it readable, understandable and consistent. Be sure to include column headers, labels (use descriptive labels, not "#5" to correspond to this instruction). Use Excel menu items, functions and formulas in every instance where appropriate
The weighted course grade will be determined by using the following:
Assignments = 25%
Quizzes = 5%
Test 1 = 20%
Test 2 = 20%
Test 3 = 20%
Final Exam = 10%
Your work should meet the following criteria:
a) Sort student records by last name.
b) Truncate the student ID to the last four characters.
c) Any cells containing data that you suspect may indicate a problem with data integrity should be flagged (highlighted in yellow). Do not correct the records, only highlight them.
d) Calculate each student's numeric course grade in a column.
e) Populate one column with each student's course letter grade, using the VLOOKUP function.
A is equal or greater than 90
B is equal or greater than 80
C is equal or greater than 70
D is equal or greater than 60
Optional challenge, use the plus/minus grade values from your syllabus, which will make "f" below a little different.
f) Format the overall course grades using conditional formatting with different colors for A, B, C and less than C.
Report relevant statistics at the bottom of the worksheet, in the rows below the records. Minimally, you should include statistics for each test, assignment and the course overall.