Excel Project: The Grade Book

Introduction: In this project you will complete a grade book for a fictitious class. All the student scores have been entered but the course averages need to be calculated.

The student scores are based on

  • Quizzes (30%)
  • Exams (40%)
  • Homework (10%)
  • Final exam (20%)

Students are allowed to drop their lowest exam and were given two attempts at each quiz, with the highest quiz score counting for each. Ten homework assignments were assigned and the average will be computed.

You will be provided an excel work book with raw student grades pre-entered. It will be your job to calculate the scores and generate letter grades for each student.

BEWARE: Your TA (teaching assistant) is constantly working and occasionally will re-enter/correct a grade. That is to say the sheet is dynamic. Entering static values rather than formulas may not earn credit.

You will complete the following task to generate the grade book. Each task has a specific value in the assignment.

Task / Task Description (Task Value%)
1 / Compute Averages for Columns C-V (10%)
2 / Compute3 Quiz Scores (10%)
3 / Compute Exam Score (10%)
4 / Compute Homework Score (5%)
5 / Transfer Final Exam Score (5%)
6 / Compute Weighted Score (15%)
7 / Compute Letter Grade (15%)
8 / Compute Averages for Columns Y-AE (10%)
9 / Compute Average LetterGrade for Class (5%)
10 / Compute Counts (10%)
NA / All Calculations Correct (5%)

Instructions:Download the starting datafile (ProjectExcelGradebook.xslx on the website under projects) and move to the title sheet. Add your name as the author and insert your section number (you will not be able to select any other cells, they are locked). Rename the MyPhoto sheet with your name and add your photo somewhere on thatsheet.

Move to the Excel Project Sheet and complete the following tasks. The rows you will need to edit have been made a little larger and numbers have been added near each editing point to help you identify the areas. The sheet should make sense (hopefully), so look at it carefully and see the big picture before moving on.

  1. Compute averages for columns C through V on row 23.
  2. Compute the quiz scores.
  3. In cell C32 compute the quiz 1 score by taking the highest score from cells C5 and D5.
  4. In cell D32 compute the quiz 2 score by taking the highest score from cells E5 and F5.
  5. In cell E32 compute the quiz 2 score by taking the highest score from cells G5 and H5.
  6. Repeat steps (2.a, 2.b, 2.c) for cells C33 through E48.
  7. Compute the exam score in cell F32. The exam score will be the exam average after dropping the lowest score. We can find this value by subtracting the smallest from the sum and dividing the result by two. Repeat for cells F33 through F48.
  8. Compute the homework score in cell G32 by taking the average of cells L5 through U5. Repeat for cells G33 through G48.
  9. The final exam score belongs in cells H32 through H48. In H32 use the formula =V5 to transfer the value of V5 to H32. Repeat for the other cells.
  10. In cell I32 we want the weighted score. The weights for each column can be found on row 29 above each column. Each score on row 32 should be multiplied by its associated weight (you may use an absolute reference or a constant) and the result summed to generate a score. Each score will be between 0 and 100.

The formula will have this form:

=score1*weight1+score2*weight2+score3*weight3+….

where scores and weights are appropriate cell references and or constants.

  1. Compute the letter grade. Now that we have a score generated we just need to convert that into a letter grade using the grading scale in L30 through N36.

Enter the following formula for cell J32:

=VLOOKUP(I32,$L$32:$M$36,2)

Explanation of VLOOKUP(first parameter,second parameter,third parameter) follows.

VLOOKUP stands for vertical table lookup function. We have a lookup table in cells L32:M36. That’s the second parameter of the function. The $’s allow us to copy the table cells down without changing the coordinates. The table location is constant. Given a grade we can use the table to lookup a value. I32 is the value we want to look up. That’s the first parameter of VLOOKUP. Column 2 of the lookup table contains the value we want to pull out; the letter grade. So 2 is the third parameter of the VLOOKUP function. VLOOKUP has an optional fourth parameter but as we do not want an exact match on the lookup value we left it blank.

  1. Compute column averages in cells C49 through I49.
  2. Use VLOOKUP to find the letter grade on the average final grade (cell I49).
  3. Compute grade distribution in cells N32 through N36. We want to report the number of A’s, B’s, C’s etc. We will use the COUNTIF(parameter1, paramter2) function.

The first parameter is the range. The second parameter is the test. In N32 we can use

=COUNTIF(J32:J48,"=F")

To count the number of F’s in column J. You cannot copy this formula down to calculate the count for D’s, C, B’s and A’s so you can either retype it in each cell or make a small modification:

=COUNTIF($J$32:$J$48,"="&M32)

This will do the trick. “” is the concatenation operator in Excel. We can use it to grab the letter grade we want from the adjacent cell and putting $ in the range makes an absolute (so it won’t change when we copy it down) cell reference.

If everything is correct you should see a sore now on the title sheet. You can also see which steps contain errors. Make corrections as necessary and submit.