In this exercise, we'll work with spreadsheet formulas, functions and relative/absolute addresses using Microsoft Excel.

To begin, download this sample Excel workbook onto your H: drive in your FIT folder. The workbook includes one spreadsheet named "grading".

In this exercise, produce a gradebook spreadsheet that looks like this sample gradebook. Start by studying this sample. Make sure you think about where you're going before you do anything else. When you're ready:

• Add the appropriate weights to the "weights" row, and sum them up in cell G2 to ensure that they add up to 1.0. To do this sum, use the function call:

  =SUM(B2:F2)

  where B2:F2 is the range of cells for all the weights.
• Format the weights and their sum as percentages. Note: The sample gradebook shows the raw values, without the appropriate formatting! Be sure to update the formatting!
• Add the "assignment averages" row that computes the project average for the scores in that column. For this, use the function call:

  =AVERAGE(B5:B12)

  where B5:B12 is the range of cells for all the "proj1" scores. Fill this in to the first average cell and then copy it across the averages row using the "fill handle" discussed in the videos. Remember: Be lazy, don't do the math yourself, and don't hand-type the formula again and again. If these instructions seem unclear, Prof. Norman has created a video showing exactly what you are trying to do, which you can view by clicking here.
• Compute the weighted score for the first student's row using an appropriate formula. Each project score should be weighted by the appropriate project weight. So, for example, the weighted contribution of proj1 will be computed by

  B5*B2

  • The formula for the total score should therefore add the weighted contribution of each assignment as follows:

    =B5*B2 + C5*C2 + <... terms for the other projects and the final...>
    					

  • We want to use the fill handle to compute the weighted scores for the remainder of the students. However, as it stands, each of the terms in the formula above will change. We want the weight values to remain the same, but the student scores to change. Use the F4 key (or type the "$"'s yourself) to turn each of the project weights into an absolute reference. So, for example, B2 becomes $B$2. Stop to think about what you are enforcing: The weight value $B$2 should be the same for all students, whereas the score, B5, should adjust as the formula is copied.
  • When this is right, copy the formula down to the other students' rows using the fill handle.
• The weighted score computation you just entered has a bit too much typing and won't automatically upgrade itself if you add a new column—it's not lazy enough! To explore an alternative, replace the formula for the first student's weighted score, and use the SUMPRODUCT function, as follows:

  =SUMPRODUCT(cell range of assignment weights, cell range of assignment scores)

  Note: You should get exactly the same answer you had before -- you are simply using a different implementation. Don't forget to use relative and absolute references as appropriate. Copy the formula down the column using the fill handle. As a general rule, you can expect Excel to have functions specialized to deal cleanly with situations like this, so be sure to look for them when you feel that you're not being "lazy enough".
• Finally, sort the student rows by selecting all rows 5-12, including the students' names and weighted scores, choosing "Data"-"Sort" and selecting "Column G", "Descending". (Troubleshooting hint: you'll get interesting results if you didn't use absolute addressing correctly.)
• Change the grader's name to yours.

Be sure to save your modified workbook on your H: drive.

Though you might consider uploading this spreadsheet to Moodle, instead let's incorporate your work into your website:

1. Login to your Calvin Google Account (that is, your student mail)
2. From the top list of options, choose "Drive" or "Documents"
3. From the right hand side, click the gear symbol to open Settings:
   1. Hover over Upload settings
   2. Choose "Confirm setting before each upload"
4. On the left hand side, next to the button labeled "Create", click the "Upload" button (it looks like a disk drive with an up arrow)
   1. From the popup menu, choose "Files..."
   2. Browse to your H: drive, and then your gradebook spreadsheet
   3. Choose "Open"
   4. From the Upload settings, choose "Convert documents ... to Google format"
   5. Click "Start Upload"
5. Your grade book spreadsheet should now be listed under "My Drive". Open the spreadsheet by clicking on the title (the icon next to the document should be a square -- if it is an X, you did not convert the document to Google format as instructed). Spend a couple minutes making sure that the import worked correctly (all cells have the correct formula and/or value) and everything is still formatted as it should be. Leave this page open!
6. Open Dreamweaver, and create a new page called fitAssignments.html in your website folder on your H: drive.
   1. Title the page "FIT Assignments"
   2. Make your name an h1 header in the content of your page
   3. Create a bulleted list and make the first entry "Grade Book Spreadsheet"
7. Back in your web browser, on, in the upper right corner, click "Share"
   1. Copy the "link to share" from the top of the screen and use this to make the "Grade Book Spreadsheet" item an absolute link on your FIT assignments page in DreamWeaver
   2. In your web browser, add your professor and grader as collaborators, but *DO NOT* send them a message
   3. Click "Share & save" in your browser
   4. Save your modified FIT assignments page in Dreamweaver
8. Upload your modified FIT assignments page to Calvin's webserver (instructions from first website exercise)
9. Submit via Moodle a link to your FIT assignments webpage.

We will grade this assignment according to the following criteria:

• Completeness:
  • 5% - Weights are formatted as percentages
  • 10% - Sum of weights is present and computed using SUM
  • 10% - Averages are computed using AVERAGE
  • 20% - Weighted scores are computed using SUMPRODUCT, relative and absolute references
  • 20% - Grade book is correctly sorted by weighted score
  • 5% - Grader's name is present
• Accuracy:
  • 10% - Each sum, average and weighted score computed is correct.
• Web:
  • 10% - Submission page is named and titled appropriately
  • 10% - Link to spreadsheet works and appears in a bulleted list