In this lab 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 **worksheet** named
"grading".

In this exercise, produce a gradebook worksheet 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:

- Row two contains
*weights*for each of the assignments. Label these for clarity by specifying "Weights" in A2. Now 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. Again, add a label in the appropriate position, and then 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 class. 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*in Column G for the first student's row using an appropriate formula.- This formula should take the assignment weights as
follows:
=B5*$B$2 +

where*terms for the other projects and the final*`B5*$B$2`

computes the weighted contribution of proj1. (Use the F4 key on your keyboard to turn`B2`

into`$B$2`

.) Note the use of the absolute reference`$B$2`

to refer to the weight of proj1. That weight value should be the*same*for*all*students, whereas`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.

- This formula should take the assignment weights as
follows:
- 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! As a
contrast,
the formula for the first student's weighted score, and use the*replace*`SUMPRODUCT`

function, as follows:=SUMPRODUCT(

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".*cell range of assignment weights*,*cell range of assignment scores*) - 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 in cell A16.

Be sure to save your modified workbook on your `H:`

drive.

Submit your modified gradebook spreadsheet in Moodle.