Project #10 : A Class Gradebook

This project uses a spreadsheet to create a gradebook that displays the students grades on tests, calculates weighted averages for student grades and tests, and assigns letter grades to the student percentages.

A typical gradebook has the names of the students in the class, a list of the tests and quizes with the grade that each student received, averages of the tests, and the average that the student has in the course.

The instructions to construct the spreadsheet

1. Create a new spreadsheet file called GRADEBOOK.
2. Type in the title, (10) names, and Column titles, and names that you make up. It may look like the following:            

            A          B        C       D        E       F      G     H        I
   1                      Gradebook for 3rd Grade Science
   2  Students:      Test 1  Test 2  Test 3  Test 4  Test 5  Test 6 Wt Ave Ave.
   3  Hooper, Betty
   4  Bakker, Carol
   5  Helpful, Harold
   6  Heart, Larry
   7  Mighty, Mike
   8  Mouse, Minny
   9  Adams, Sally
   10 Que, Susie
   11 Terrific, Tom
   
   Save your work to the disk.!

3. Next, use the spreadsheet to sort or arrange these names in alphabetical order:

Click on the Block menu selection and select the Sort Option.

With the mouse block A3 to H12. [Position mouse at A3 hold down mouse left button release it when you are on H13].

Click on 1st key cover the part of the first column with student names.

Now click on OKAY which does the sorting.

See the result:

        A          B        C       D        E       F      G     H       I
1                      Gradebook for 3rd Grade Science
2    Students:   Test 1  Test 2  Test 3  Test 4  Test 5  Test 6  Wt Ave  Ave
3  Adams, Sally
4  Bakker, Carol
5  Heart, Larry
6  Helpful, Harold
7  Hooper, Betty
8  etc.

4. Enter scores for the first test.

        A          B        C       D       E       F      G     H       I
1                      Gradebook for 3rd Grade Science
2    Students:  Test 1  Test 2  Test 3  Test 4  Test 5  Test 6  Wt Ave  Ave
3  Adams, Sally     76
4  Bakker, Carol    67
5  Heart, Larry     78
6  Helpful, Harold  89
7  Hooper, Betty    87
8        etc.


Note that entering these scores truncate the names. Fix this by setting the width of column A to 15.

Tests are integers with 2 or max 3 digit numbers, set the width of the test columns to 6 .

5. Enter possible scores for tests up to test 6.
Leave some scores blank for excused tests.
6. Put the average functions into the spreadsheet.
   1. Go to the Ave cell [I3] for Adams, Sally row. Ave, and type in the formula: @AVG(B3..G3)
      
      (It is best to type @AVG(cursor to B3 anchor with a . cursor to G3 and press ) or one may cover the cells for inclusion inside the parenthesis.
      The beauty of this formula is that a cell without an entry or number is not counted be used in the average. This is good for an excused absence. If a person was unexcused for a test and received a 0 then out a zero in that test cell and it will be averaged into the grade.
      

   2. Check to see that the test ave. is correct( i.e. no mistake in the formula).
   Do this by putting in easy grades and averaging them by hand and check the computer's answer.
   
   3. Copy the formula for Sally's test average down to all the student test averages. The cells in the I column cells.
   4. Remember you can copy a range of cells into new locations or copy one cell into many locations with one copy command. Click on Edit Copy then mark the source cell and the destination cells. Check that there are no glaring errors.

7. Enter the a Weighted value for the Midterm (test 3) and the Final (Test 6)
• Two of the tests are weighted.
  Test 3 counts 50% more than the other tests (worth 1.5 tests).
  The Final counts double the other tests (2 times its value).

• Make the I column the weighted average (column H)
  Put in weighting formula for Sally: +(B3 + C3 +1.5* D3 + E3 + F3 +2*G3)/7.5
  (Note : This formula is not correct for an excused absence.
  A big difference between the Test Ave. And the Weighted Test Average would signal this error.)

• Copy the weighted average from row 4 to all the other students.
8. Now Print Out a copy of the gradebook

First get everything in the spreadsheet cleaned up, check averages, and write notes about things to watch and print out a copy of the spreadsheet.

9. Next,sort the students in the spreadsheet by highest weighted Test average from highest to lowest.

Change the primary key to the Test Average column and PRINT OUT THE GRADE BOOK.

10. Add a row at the bottom of the spreadsheet for the averages of each test.
• In the row for Test Ave below the last name in column A, type Test Ave.
• In the next column and on the same row type in a formula like @AVG(B3..B14).
• Copy this formula under each test column.
11. Add another row at the bottom of the spreadsheet for the Standard Deviation of each test.
• In the row under Test Ave in column A type the Title Standard Deviation.
• In the next column in the same row type in a formula like @STD(B4..B14).
• Copy this formula under each test column.
• PRINT OUT GRADE BOOK
• Clearly Note other interesting things in the sheet with hand written notes and do a hand check for on average to see if it is correct. Write this check on the printout.

         A          B        C       D        E       F      G       H       I
1                      Gradebook for 3rd Grade Science
2  Students:      Test 1  Test 2  Test 3  Test 4  Test 5  Test 6   Wt Ave   Ave
3  Adams, Sally     76
4  Bakker, Carol    67
5  Heart, Larry     78
6  Helpful, Harold  89
7  Hooper, Betty    87
8      etc.
9  Test Average   78.7
10 Standard Dev    6.48

12. Do a three dimensional graph of Sally's test scores and the average scores of the tests
• Choose a 3-D bar graph
• Use Sally test scores and the class test averages as series on the graph.
• Set Sally's 6 tests as Series 1
• Set the class's test averages as Series 2.
• Set the x-axis as the column of titles B2..G3.
• To set the Legend Series 1, type Sally Adams and for Legend 2, type Class Average.
• Add nice titles. PRINT OUT THIS GRAPH IN LANDSCAPE AND PORTRAIT MODE.
• On which two tests did Sally do better than the class average?
13. Make the Gradebook Readable
• Since your principle has difficulty reading your gradebook, he wants you to modify the spreadsheet so that there will be a row separating each student. (See Below).
• Insert a row between Sally and the label Student.
  
  • Put your cursor on Sally and insert a row between Sally and Student.
  • Insert a line between each student.
  • Put s line of -'s between each student.
    Move the cursor to the row before Sally and put /- in the first cell.
    Copy this cell across the column.
    
  • Repeat this for each blank row.

         A          B        C       D        E       F      G      H      I
1                   Gradebook for 3rd Grade Science
2    Students:     Test 1  Test 2  Test 3  Test 4  Test 5  Test 6  Wt Ave Ave
4  -----------------------------------------------------------------------------
5  Adams, Sally      76
6  -----------------------------------------------------------------------------
7  Bakker, Carol     67
8  -----------------------------------------------------------------------------
9  Heart, Larry      78
10 -----------------------------------------------------------------------------
11 Helpful, Harold   89
12 ----------------------------------------------------------------------------
13 Hooper, Betty     87
14 -----------------------------------------------------------------------------

etc.

18  Class Average  78.7
19  Standard Dev   6.48

14. Build a lookup table to assign a letter grade for each student.
1. The table below is an example of a vertical look-up table.
   The first column is the average percentage and the second column is the letter grade associates with that percent. (See Table Below).

2. A student's average percentage that is equal to or grater than the value in the first column will be assigned the letter in the second column.
3. The look-up table should be placed in cells that are below the grade book information.
   You might choose A35..B45.
4. The percentages and the letter grades from the table below should be typed into the cells in the range A35..B45.

0	F
65	D
67.5	D+
70	C-
72.5	C
77.5	C+
80	B-
84	B
89	B+
92	A-
96	A

5. Add an additional column to your gradebook labeled Letter Grade.
6. For each student's row place the formula that calls the look-up table.

@vlookup(Cell address for the % grade, look-up table range,1)

7. For Example:

   For the student whose data is in row 5 of the grade book and the % grades is in I5.
   The function in J5 would be @VLOOKUP(I5,A35..B45,1).

8. Copy this formula for all the students.
9. Print out a final copy of the complete grade book.

           Return to CSC 171 Syllabus

This page is Copyright 1998 by J. Barry DeRoos and Robert Barrett. All Rights Reserved.
This page may be distributed and used freely, provided that the copyright notices remain intact.
Last Modified: Nov 14,2001