Dr. Diane E. Newby
Central Michigan University
EDU 590
Last Update:  May, 2007

Preparing to Create the Spreadsheet Sample Grading Spreadsheet

1. Use a spreadsheet application such as Microsoft Excel to calculate the final grade for each student.

2. Use Rows 1 through 6 for comments, a title for each assignment, a row to insert the percentage amounts, and the name of the document.

3. Start on Row 7. Type in 10 names of students and their scores for the following assignments:

a. five homework scores @ 15% of final grade
b. three quizzes @ 65% of final grade
c. one test @20% of final grade
4. Give your spreadsheet document a title.
5. Follow the example below to enter your data.
6. On row 4, enter your weighted scores as:  15% column G, 65% column L,  and 70%, column N.  The weighted scores are the percentages of the final grade for homework, quizzes and the test.  
7. Your final grade should have a formula indicating the percentages for the assignments. For example, look at the final grade for Harold A. If you click on the final grade column, the formula bar will show that a percent was multiplied by the average score for each assignment.  Notice the $ signs used in the formula.  See the yellow box below with the formula.
8. Once the average score is calculated for G7, highlight G7 through G16.  Select, Edit, Fill Down .  You should see the average for G8 through G16 .

9.  Develop a Lookup/grade array.  See the array below, starting with G19, label the grade array.  In cells G20 to G 30 and I 20 to I 30 type in the grade array.
 
Click on cell P7 and type in this formula.
=LOOKUP(07,$G$20:$G$30,$I$20:$I$30)
Hit Return.
Highlight cells P7 through P11.
Select Edit and Fill Down.

Note:   If you are entering scores for 10 students,  you will highlight Cells P7 through P 16.
10.  Insert a comment.  Follow the instructions in the yellow box in the example below.
11.  Insert a chart. for the final grade Column O. (the letter O)  Use your Excel Help menu.

If you still meet with problems in creating your spreadsheet, here is one that is started for you. Click here to download it to your floppy disk or zip disk. You just need to continue filling in the data, such as students' names, homework scores, quiz scores, and final grades. Use this example to practice. Now create your own spreadsheet with different names and scores to meet the requirements of the spreadsheet assignment.  Be sure to change your weight scores on row 4 to: Download the spreadsheet showing the "maximum" formula. Click here.

 Tutorials | Newby's WebConnection