Now that I’ve talked about how to keep references from being relative, and the sort of tricks that allows you to do, I’m going to talk about a fun little trick that relative formulas let you do quickly and easily: incremental formulas.
Incremental formulas are formulas where there are a lot of identical (but relative) formulas, and the results of one formula depend on the previous one. The Fibbonaccci sequence is one example outside of spreadsheets. For those of you who aren’t familiar with it, the Fibbonacci sequence is an interesting sequence of numbers: 0, 1, 1, 2, 3, 5, 8, 13, and so on. It’s interesting because it occurs a lot in nature, but can be derived by a simple set of rules.
Rule 1: The 1st number is 0, the second number is 1.
Rule 2: All subsequent numbers are the sum of the previous two numbers.
That is to say, the third number is 0+1=1, the fourth is 1+1=2, fifth is 1+2=3, sixth is 2+3=5, and seventh is 3+5=8.
You can actually create the Fibonacci sequence in a spreadsheet very easily, by taking advantage of relative references. A1=0. A2=1. A3=A1+A2. Then copy A3, and paste it down as many rows as you like. The formula will automatically change so that A4=A2+A3, A5=A3+A4, and so on.
When I created the 9×9 multiplication table last week, I didn’t type in all the numbers for the row and column headers. I just typed two formulas: B1=A1+1 and A2=A1+1. Since A1 was empty, that counts as zero, so A1+1=1. Then I copied B1 and pasted it in C1:J1, and copied A2 and pasted it in A3:A10. Tada – the headers were set up, and I didn’t have to type them all by hand. You could argue that it wouldn’t be much harder to do it by hand, and that’s true for a 9×9 table, but what about a 20×20? 100×100? Plus, as always, using formulas means minimizing the risk of typos. For example, if I were hitting a number, then hitting tab to move to the next cell, and repeating the process until done, it’s entirely believable to me that my “number” finger would be a titch faster than my “tab” finger on one entry. So I might end up with 45 in what should be the “4” column, and nothing at all in what should be the “5” column.
Another thing I do with the basic A1+1 formula is creating “order” columns. Sometimes I’m working with information that I know I’ll want to sort, but it still matters what order it was originally in. One good example of this is the finding files project I shared a few weeks back. Another is when I’m proofreading manuscripts – sometimes I only need to look at certain lines, such as lines that contain ( or ) when I’m checking citations, but it will still matter what order they were originally in. So I can incrementally number the lines using this formula, copy & paste it as values over itself, and then no matter how I sort it I can always go back to the original by sorting by the “order” column.