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:
=SUM(B2:F2)where
B2:F2
is the range of
cells for all the weights.=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.
=B5*$B$2 + terms for the other projects and the finalwhere
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).SUMPRODUCT
function, as follows:
=SUMPRODUCT(cell range of assignment weights, cell range of assignment scores)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".
Be sure to save your modified workbook on your H:
drive.
Submit your modified gradebook spreadsheet in Moodle.