CSE610 - Day 3 - Excel - Gradebook Description

Gradebook with Weighted Grades - Using Excel
      

Setup Considerations
Before setting up a weighted gradebook, one must make some determinations about assignments and grading. Among those decisions are the following:
  • What assignments, homework, tests, projects will be included in the gradebook
  • What weight or percentage of the total final grade will each assignment or entry be given; for example, if the final test will be worth 25% of the total course grade, the weight would be 25
  • What is the maximum points that will be given to each assignment; for example the final will be graded on the basis of 100 points, hence the maximum is 100, whereas the book review may be graded on the basis of 20, so the maximum would be 20
  • What is the range for each letter grade; for example 90-100 is an "A", 80-89 is an "B", etc.

     Let's consider the following model. The gradebook will include grades for the final test, a book review, three assignments and participation in ten class periods.
     The final will be worth 25%, the book review 20%, each assignment 15% and each day of participation 1% for a total of 100%. (This must add to 100%.)
     The final test will be graded on a maximum of 100 points, the book review and each assignments can receive up to 20 points and the daily will be graded on the basis of 2 points (good participation, some participation and poor participation).
     We'll let 90's be "A", 80's "B", 70's "C", 60's "D" and below 60's "F".


Inital Setup
Setup columns. This may vary from application to application.
  1. Blank
  2. Last name of the student
  3. First name
  4. Assigned student number, i.e. the last four digits of the V-number at WOU
  5. Letter grade
  6. Total class score
  7. Book review weighted score
  8. Assignment one weighted score
  9. Assignment two weighted score
  10. Assignment three weighted score
  11. Final test weighted score
  12. Total participation weighted score
  13. Blank
  14. Actual book review score
  15. Actual assignment one score
  16. Actual assignment two score
  17. Actual assignment three score
  18. Actual final test score
  19. -28. Daily participation actual scores
Setup rows
  1. Blank
  2. Headings or titles of each column
  3. The maximum possible points for each assignment
  4. The weight of each assignment
  5. A sample "ideal student" (for testing the computations)
  • A row for each student
  • A blank row after all the students
  • A row for counting the assignments
  • A row for averaging grades
General Setup
     I typically color the borders and blanks rows and columns a different color so I can quickly focus in on the grades. I also color the second through five rows another color for the same reason as well as columns that I want to "jump" out at me for quick reference.

Entering Titles, Basic Values and Students' Names
  • Enter students' names and numbers as appropriate in columns B-D in rows 6 through whatever (for this assignment enter about ten fictitious names).
  • Enter the appropriate headings or titles for each column in row 2.
  • Enter the maximum possible points for each assignment in row 3 and the weights in row 4
  • Name the sample student Asample and enter perfect scores for this student in columns N through AB.
  • Select (highlight) cells and use Format/Cells to add color and align text. Highlight a column (by clicking on the letter at the top of the column) and use Format / Columns to change its width.
Your project may look something like this:



Entering Formulas
Now we will be thinking a bit of math and a bit of logic.
  • In cell G5 (that's the first weighted score for our sample student, if you choose a different layout and don't end up in G5), enter the formula =N5/G$3*G$4 . What is happening is you're dividing the student's score (in cell N5) by the maximum possible score (in cell G3) giving you the percentage score for that student's assignment and then multiplying it by the weight given to that assignment (which is in cell G4). Why the dollar signs? In a moment we are going to quickly and easier replicate that formula to all students and while the N5 will reflect the particular student's score by changing to N6, and N7, and so on, the G3 and G4 cell must remain absolute. The dollar sign does that.

  • After pressing Return or Enter, click on that same cell again. Notice as you drag your cursor to different parts of the cell, three different cursors appear. The hand will allow to drag the contents of that cell somewhere else. Don't do that. The big plus is the primary cursor, but the third (its shape varies from computer to computer) appears when you near the lower right corner of the cell. When it appears, click and drag to the right over the cells for the three Assignments and the Final. Release the mouse. Notice that the same formula is now in all the cells.

  • In the Participation cell, which we just ignored, type =sum(S5:AB5)/L$3*L$4 and press Enter or Return.

  • In cell F5 type =sum(G5:L5) and press Return/Enter. This should be 100.

  • In cell F4 type =sum(G4:L4) and press Return/Enter. You could highlight F5 and copy and paste it into F4. This should also be 100.

  • Click and drag your cursor across F5 to L5. Release. Move to the special cursor in the lower right corner of L5. Click and drag that cursor down over all the rows of students. Release. Now the formulas are in all the cells.

Before we do the hard part, you should be seeing something like this. That dragging may have messed up some formatting so you have to clean that up.



Entering More Formulas
This is more logic. We're going to use the statement "if the grade is less than 90, the grade is not an 'A', but it that's not true it will be an 'A'. In the spreadsheet cell E5, we will write =IF(F5<90,"not A","A"). But we're going to compound that by putting this formula inside of itself replacing the "not A" to recognize other letter grades. Trust me on this one.
  • In cell E5 (that's just to the left of the F4 where we put the total class score several steps earlier), enter the formula =IF(F5<90,IF(F5<80,IF(F5<70,IF(F5<60,"F","D"),"C"),"B"),"A") . Click and drag this cell to replicate this formula for all the students.

  • Highlight the cell in the Counts row in the First Name column. The top menu bar, select Insert and then Function. When the new little window appears, select All in the left column and COUNTA in the right column. When the next window appears, click on the little triangle to the right of the first textbox; and then highlight all the first names. Click on that same little triangle and click OK.

  • Repeat the above process in the Counts row in the columns where you will be entering the scores using the COUNT function. COUNTA counts words, COUNT counts numbers.

  • Repeat the above process in the Averages row in the appropriate columns using the AVERAGE function. Until you enter scores this may look crazy.

The sample below includes some scores for one completed assignment and all scores for one student so one might see how this works.


Save in public_html (P:drive) after it is complete. Also save on your desktop and upload into Moodle.

Putting in on the Web
To share this information with the students and parents, you may put this on the Internet, obviously without names. Use only their numbers.
  • Highlight the entire rows of all the students by clicking on the number in front of the first row with student information and dragging down to the last row with student information. In this sample that would be 6-17. Be certain that the entire row is highlighted and not just a couple columns.

  • Select Data, then Sort from the menu bar. Sort on the column with the students' numbers (probably column D).

  • Highlight all the students' scores including the headings but certainly not the names. In this example that would be from cell D2 to AB17. Notice that counts and averages were not selected as you may not want to share that information.

  • Select File and then Save as Web Page in the menu bar. Save in your public_html folder and give it an appropriate name making certain that the .htm or .html is on the end of the name.

  • Check your work online by going to your webpages at www.wou.edu/~username.