statistics on excel

Excel: Grade Book

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. Please follow the instruction below to complete the assignment.

a. Open e02m3Grades and save it as FirstnameLastname_Grades.

b. Use breakpoints to enter the grading scale in the correct structure on the Documentation worksheet and name the grading scale range Grades. The grading scale is as follows ( Hint: ascending order, breakpoints.)

c. Calculate the total lab points earned for the first student in cell T8 in the Grades worksheet. Copy the function down to row 30.

d. Calculate the average of the two midterm tests for the first student in cell W8. Copy the function down to row 30.

e. Calculate the assignment average for the first student in cell I8.The formula should drop the lowest score before calculating the average. Copy the function down to row 30. ( Hint: you need to use a combination of three functions: SUM, MIN, and COUNT. The argument for each function for the first student is B8:H8. Find the total points and subtract the lowest score. Then divide the remaining points by the number of assignments minus 1. The first student’s assignment average is 94.2 after dropping the lowest assignment score. (SUM-MIN)/(COUNT-1).)

f. Calculate the weighted total points based on the four category points (assignment average, lab points, midterm average, and final exam) and their respective weights (stored in the range B40:B43) in cell Y8. Copy the function down to row 30. (Hint: use relative and absolute cell references as needed in the formula).

g. In cell Z8, use VLOOKUP to figure out the letter of grade. Copy the function down to row 30.

h. Name the passing score threshold in cell B5 with the range name Passing. Use an IF function to display a message in the last grade book column based on the student’s semester performance. If a student earned a final score of 70 or higher, display Enroll in CS 202. Otherwise, display RETAKE CS 101. Copy the function down to row 30.

i. Calculate the average, median, low, and high scores for each assignment, lab, test, category average, and total score. Display individual averages with no decimal place; display category and final score average with one decimal place. Display other statistics with no decimal places.

j. Insert a list of range names in the designated area in the Documentation worksheet. Complete the documentation by entering your name, current date, and a purpose statement in the designated areas.

k. Select page setup options as needed to print the Grades worksheet on one page. Change the page orientation to landscape.

l. Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet.

m. Save, close the workbook, and submitted on Blackboard.

After complete, your workbook should be similar with the figure shown below: