Computing Your Grade

This will also give us some insight into the strength of a spreadsheet.

• Joe Average has
  • completed 6 of the 7 Practice exercises with scores of 10, 10, 10, 9, 8, 8.
  • completed 4 of the 5 other exercises with scores of 100, 100, 90, 95.
  • Scored 86% on the first test and 82% on the second test.
  • Planning and Setup
    • Take a look at the syllabus, specifically the Grades section.
    • There are four categories of grades
      • Practice Exercises
      • Other Exercises
      • Regular Test
      • Final Exam
    • Let's plan on computing the averages for each of the four categories in the gradebook.
    • We can do this By setting up a row for each measure.
    • The final average is a Weighted Average .
      • The weights should total to 100 (or 100%)
      • We compute averages for the individual components
      • The multiply this by the weights.
      • To get the final average.
    • We can set up a table to compute the final average.
  • First set up the table for the final average
    • Somewhere at the top of the worksheet, add the four categories, and the weights for the four categories.
    • 
    • Noticed I changed the angle of the label so it fit.
    • I also included a percent sign with the data.
    • This data came from the syllabus.
    • We will add another column later.
  • Set up the area to calculate the Practice Exercise average.
    • I will do this in a row, but we could do a column as well.
    • Start by labeling the area
    • Then a spot for each result.
    • But let's be a bit more flexible, let's set up space for the entire semester.
      • How many spaces?
        • It looks like we do about 1 a week, but to be sure, let's make space for 16 grades in this category.
      • Label the cells 1-16
      • Add a location for total points and one for Average.
      • 
      • We could just compute the average for the scores we have,
        • But this would be messy as we add more scores.
        • Let's use the count function to compute points possible.
        • =count(b10:q10)*10
        • This works because blank cells are not counted.
        • This is for my worksheet, the location may change for yours
      • Computing the average then is
        • This works because blank cells are treated as a 0 in sum.
        • =sum(b1:q10)/r10
  • We can do the same thing for the Other exercises.
  • I made a row for the tests.
    • Since the tests are given as a percent, we can just average them
    • In this case I used the average function
    • =average(b16:d16)
    • This works because blank cells are not included in an average.
    • There is a place for the last test.
  • Make sure you put a place for the final exam.
    • In this case, I used the test average as the final exam score
      =f16
    • 
    • Here is the same worksheet in formula mode
      • Note, I have hidden some of the columns.
      • 
    • Now compute the final average.
      • Calculate the contribution of each portion of the score
        • Multiply the average by the weight for each
        • Make sure everything is in the same units
          • Since we are computing averages, everything is an average.
          • But if we were dealing with points ....
      • Finally, sum the contributions to come up with the final total.
      • 
      •