Chp 2 - Professor's Grade Book - v.May 2015
You are a teaching assistant for Dr. Denise Gerber, who teaches an introductory C# programming class at your college. One of your routine tasks is to enter assignment and test grades into the grade book. Now that the semester is almost over, you need to create formulas to calculate category averages, the overall weighted average, and the letter grade for each student. In addition, Dr. Gerber wants to see general statistics, such as average, median, low, and high for each graded assignment and test, as well as category averages and total averages. Furthermore, you need to create the grading scale on the documentation worksheet and use it to display the appropriate letter grade for each student.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Download and open the file namedexploring_e02_grader_h4.xlsx.
In the range A7:B9 in the Documentation sheet, enter the breakpoints for the C grades. In row 7, enter 70 as the breakpoint for C-; in row 8, enter 73 as the breakpoint for C; and in row 9, enter 77 as the breakpoint for C+.
Assign the name Grades to the range A3:B14 in the Documentation sheet.
Hint: Create a named range.
In cell R3 of the Grades sheet, calculate the total lab points earned for the first student in the Grades worksheet. Lab assignments are indicated by the column labels L1 through L8. Then copy the function to the range R4:R21.
Hint: - There is no such function in Excel called TOTAL(). Instead, use the function that can be used to add up values stored across a range of cells. - To copy the function, drag the fill handle in cell R3 to cell R21.
In cell U3 of the Grades sheet, calculate the average of the two midterm tests for the first student. Midterm tests are indicated by the column labels M1 and M2. Copy the function to the range U4:U21.
In cell I3 of the Grades sheet, calculate the assignment average for the first student. Assignments are indicated by the column labels A1 through A7. This formula should also accommodate the syllabus rule that in calculating the average grade, the lowest score will be dropped from the calculation. Then copy the formula to the range I4:I21.
Hint: Use a combination of SUM(), MIN(), and COUNT(). The argument for each function for the first student will be B3:H3. Find the total points (SUM()) and subtract the lowest score (MIN()) from the result. Then divide the remaining points by the number of assignments (COUNT()) minus 1.
In cell W3 of the Grades sheet, calculate the weighted total points based on the four category points (assignment average, lab points, midterm average, and final exam, in that order) and their respective weights (stored in the range B30:B33). Use relative and absolute cell references as needed in the formula. Copy the formula to the range W4:W21.
Hint: - The formula in cell W3, once perfected, will be replicated down to apply it to the other students. When replicating a formula, you should always consider whether the use of absolute cells referencing is warranted. - The formula in cell W3 refers to the weights for the assessment components, which are recorded in the range A30:B33. In this case, there is no need to use a Lookup table. - Do not use SUM()