References in spreadsheets

Imagine you have a spreadsheet of student records as follows:

	Raw Scores
Name	Attendance	Quiz 1	Quiz 2	Quiz 3	Mid-Term	Finals
Swift, Harold	85	85	85	85	85	85
Reese, John	87	95	75	85	85	85
Carter, Josiah	87	80	90	85	90	87
Wesley, Alistair	66	95	75	90	75	93
Greer, John	94	85	95	90	80	87
Clark, Henry	78	75	80	80	85	88

Note: Names are inspired by characters from my favorite TV series, Person of Interest, and books.

These are essentially raw scores, but you want to convert them into weighted percentages which you can add later to become the final grade of each student.

Relative References

In paper, you will have to apply a formula for each cell. However, in a spreadsheet application, you can just create a formula for one cell, then copy that cell to the following cells. For example, if the weight of attendance to the overall grade is 20 percent, you can tell the computer to multiply the value of each cell in that column by 0.20. To do this, you use the reference of that cell, which is like the address of that cell in the whole spreadsheet. The reference is a combination of the column letter and row number in your spreadsheet application, like A2, meaning first column (A), and second raw (2). Here is the example:

Notice in the above picture that the formula used B2, the reference of Harold Swift's attendance raw score of 85. After hitting Enter, you can just copy that cell downward, because the spreadsheet will copy the formula, but change the value down to B3, B4 and so on.

Currently, the reference used in the formula is "relative" reference. Relative reference means that the reference will change if the copy is copied to another cell. If the target cell (where the value 85) is moved (i.e., cut and pasted somewhere else), the reference in the formula will change so that it still references to that value.

If you copy the formula to the right column, it will apply the same formula, but this time, the cell reference will adjust to the right (i.e., C2). Of course, the weight might not be the same, so you have to be discriminate in copying cells that contain formula with references.

Absolute References

What if you want to copy the same formula, but you do not want to change the reference, because you want to keep the formula really referring to the same cell? In that case, you need to use absolute references.

Take for example the revised spreadsheet, with additional columns for the weighted score:

Using references, we can use a formula to calculate the weight for Harold Swift's attendance as 

=B4*H3

This time, however, you cannot copy the formula downward, because you have a reference that is immediately above the cell where you will use the formula. In this case, you have to tell the application that the second reference, H3, should not change. This requires an absolute reference.

To make an absolute reference, you insert the dollar sign ($) before the reference that you want to make absolute. Remember that your reference is composed of a Column and a Row. You can make the absolute reference only for either, or for both. For example, in the above case, you want to make the reference for the first part (source value) to have an absolute column reference, while you want to have an absolute row reference for the second part (percent weight). So the formula would be like this:

=$B4*H$3

This way, you can copy the formula both downward and to the right. The first value's reference will adjust by row, while the second value's reference will adjust by column.

Copying the H4 cell downward:

Copying the H column to the right:

Click here to see the sample file.

References are very useful in spreadsheets because it allows flexibility in data used in  formulas and functions. For example, if you have a formula in one cell, which references another cell, any change to the source cell will affect correspondingly the formula. You don't need to worry about re-calculating it.

In the posts for the next two weeks, you will learn about two functions that use both relative and absolute references.