Excel Formulas are Algebra, part 5

If you are already familiar with Excel formulas, you don't need to bother with this post or its companions of similar title.

Now we look at relative and absolute addresses. In Part 2, we saw that when a formula is copied, cell references are adjusted in a common-sense way: If you copy a formula from B3 to B4, that refers to A3, the copy will refer to A4. Also, a copy put into H9 will then refer to G9. This is Relative Addressing.

If you have a constant factor to use in a formula, in addition to other factors to be referred to in order, AND you keep that factor in a cell, you want to be able to copy the formula, but you don't want the reference to the constant to change. For example, in a financial scenario, you might want to test it with different inflation rates. Here, prices change by a fixed amount each year. You enter the initial price in B1 and the yearly percent in B2. The years of interest are in A4 to A9. Cell B4 has the simple formula =B1. Cell B5 has the formula of interest, shown here:

Notice how, when you are editing a formula, cell references are highlighed and their locations indicated by a sequence of colors. Here, blue B4 refers to the cell just above, and green B2—but here it is $B$2—refers to the inflation percent (The other calculations convert the 3 to .03 and add it to 1).

The dollar signs in the cell name indicate Absolute Addressing. When entering a formula, you can type the cell name or click on a cell; either way, at that point, if you press the F4 function key, it changes the addressing type. In sequence, you would see

B2
$B$2
B$2
$B2
B2

and so forth. $B$2 means completely Absolute; the other two make only the Row or the Column absolute, while allowing the other to be relative. This allows for creative building of tables, a topic for much later consideration.