Relative and fixed references

A few weeks ago, when I was talking about switching the locations of two sets of cells, I mentioned the importance of using cut instead of copy, because unless you specified otherwise references in spreadsheets are relative.

Relative in this case is basically similar to how it’s used normally. For example, your brother isn’t everyone’s brother — he’s only “brother” relative to you (and whatever other siblings you may have). Also, the distance to your favorite store might be a mile, 10 miles, 1,000 miles or more, depending on where you are at the moment. That means the distance to the store is relative to your current location.

So when cell B3 has the formula =A1, what it’s actually saying as far as the spreadsheet is concerned is “this cell is equal to the one which is 1 column to the left, and 2 rows above this one.” So if you copied it to B4 it would still say the same thing as far as the spreadsheet is concerned, but since “two rows above” is now row 2, the formula in B4 would be =A2.

This is great when you’re repeating the same formula across a whole lot of cells. For example, suppose you had a grade book with 1 student per row, and 10 grades recorded for each. Assuming that the student name was in column A, and the grades were in columns B through K, if you wanted the sum of all their grades then the formula in row 2 would be =sum(b2:k2). Similarly, the formula in row 3 would be =sum(b3:k3), and so on. Because references are assumed to be relative you only have to type the formula once, and then copy it down to the rest of the rows, and it’ll automatically update which row it’s pulling the data from.

However, there are times when you want the reference not to move. One of the most common examples is a table, where the column header and row header combine to determine what’s in the cell. Fortunately, there’s two easy ways to keep the reference fixed if that’s the way you want it: named references, and dollar signs.

Fixing references with names
I’ve already discussed how to name variables, why it’s often a good idea to do so, and how functions pick which item(s) from a named group to reference.

One of the things I include in my annual report is a table showing what countries our members are from. I have one column outside of the table, named MemberCountries, which has the country code followed by the type of membership. For example, a regular member from the United States would be “US-R”. A student member from the Netherlands would be “NL-S”. This is where I’m getting all the data for the table from.

In the table itself, the row headers (i.e. the first column) are the country codes (US, NL, UK, etc.), and the column headers (i.e. the first row) are the membership types (R for Regular, S for Student, and E for Emeritus). If I want the total number of members of each type from each country, then I could use a combination of countif() and concatenate() to do so. For example cell B2 could have the formula =countif(MemberCountries,concatenate(A2,”-“,B1″)). That is to say, concatenate A2 with B1, with a dash in between, and then count the number of times that result appears in the list of all memberships. B3, however would need concatenate(A3,”-“,B1), and C2 would need concatenate(A2,”-“,C1). So the first part always has column A, and the second part always has row 1, but the other parts change depending on which row and column the cell with the formula is in.

So I make things easy on myself, and I name the first column “Countries” and the first row “Types”. The formula then becomes =countif(MemberCountries,concatenate(Countries,”-“,Types)). Or, if you’re using Open Office instead of Excel, the same except that it has semi-colons instead of commas. This formula is exactly the same in all of the cells of the table, making it much easier to fill all the cells of the table, and making it much easier to read the formula at the same time.

However, this formula won’t work in Google Docs, because in Google Docs concatenate(Countries) won’t just pull the nearest entry in Countries the way it would in Excel or Open Office. Instead, it will pull every entry. So if you’re using Google Docs or some other spreadsheet that does the same thing, you’d need to use the dollar sign approach instead of the name approach.

Fixing references with dollar signs
The alternative to names is dollar signs. Basically, in any spreadsheet if you put a dollar sign in front of the part of the reference you want to stay constant, it won’t change. If you copy the formula =A1 one cell over & down, it would turn into =B2. But the formula =$A1 would become =$A2, =A$1 would become =B$1, and =$A$1 would stay the same no matter where it went.

This is useful for situations where either names aren’t helpful (as in the Google Docs situation with concatenate), or when it’s not worth the effort.

For example, imagine you’re trying to print out a multiplication table. The top row has 1 through 9, as does the first column, and you want to multiply them in the body of the table so that B2 has 1*1, B3 has 1*2, C8 has 2*7, etc. You could name the headers, but since it’s not that complicated and you’re planning to print it out and discard the original, it’s not really worth the effort. So you could just put the formula =$A2*B$1 in B2, and copy it through the rest of the table.

One thing to remember when doing this, though – always look at the results and see if they make sense. It’s very easy to get confused as to where the $ goes, so if in this example J10 (9*9) does not equal 81, you’ve probably done something wrong.