1880 Carroll County Arkansas
Francis 25
Mathilda 22
Joseph 7

Then Annie calculated an estimated birth year for each one. She used pencil and paper, because in 1972 a calculator cost over $100, and in 1972 a dollar was worth a dollar. Not like today, when a dollar is worth about a quarter. Offer somebody a Sacajawea dollar for a tip and you'll see what I mean.

In the last RootsWorks article www.ancestry.com/library/view/news/articles/3503.asp, we talked about spreadsheets in general terms. Today, let's discuss a specific genealogical example. We'll run into a challenge early on, and learn the ins and out of copying formulas.

Today, Annie can calculate a birth year easier than before. She puts the census year, 1880, in cell A2 of a blank worksheet, and then puts the names below that, in cells A3 thru A5. She types the ages on the 1880 census in cells B3 thru B5. At this point, Annie has extracted evidence, and most people could tell whether she did it right. But Annie is about to cross a very important line. She's going to create a conclusion based on that evidence. From that moment on, the likelihood is that someone will not like what she does.

Annie is using Excel, and in cell C3 she types =A2-B3. She concludes that Francis was born somewhere around 1855. It's really hard to guess much closer when you are dealing with census data, due to rounding and lying. But seriously, can you tell how old most people are to within a year? Probably not. In other words, it's an estimate, and a really good estimate compared to, say, guessing from looking at a photo.

Nobody likes to type the same thing over and over. Neither does Annie. She wants to copy the formula for the birth year down to the next two cells. Excel has a "fill handle" in the lower right hand corner of the selected cell. The user can drag that fill handle to adjacent cells and copy the formula. It's a short cut version of copy and paste, and works the same way.

When Annie drags the fill handle for Francis' birth year down to cells C4 and C5, she sees the years 3 and 15 for Mathilda and Joseph, respectively. This is way wrong and in frustration Annie gets up from the computer, stomps over to the phone, and calls me. I'm not home. After venting into my answering machine, Annie goes back to the computer and tries to reason this problem out. She looks at the formula for Mathilda's birth year, and sees that it says =A3-B4. Well, that's not right. It should say =A2-B4. When she copied the formula, the cell references all changed, and not the way that she wanted.

Everyone discovers relative and absolute references in this manner. Simply put, a relative referencepoints to another position relative to the current cell. An absolute reference refers to another position relative to the first row or column of the worksheet. In worksheet formulae, we prefix the column letter or row number with a dollar sign, such as $A instead of A and $1 instead of 1. cell references come in four flavors:

Absolute Column, Absolute Row, $A$1, is an absolute cell reference
Relative Column, Absolute Row, A$1, is a mixed cell reference
Absolute Column, Relative Row, $A1, is a mixed cell reference
Relative Column, Relative Row, A1, is a relative cell reference

One useful tip: pressing the F4 key in Excel, while typing the cell address, will cycle it through the four flavors.

If you type a formula composed of absolute and relative references, it will give exactly the same results, until you copy it to another cell.

If Annie would click on cell C3, and then click by the A2 in the cell contents, she could press the F4 key one time and the formula would change to =$A$2-B3. If she then grabbed the fill handle and dragged it down two cells, she would see the values 1858 and 1873. This is a very simple example of a genealogical application in a spreadsheet. I'll go into more detail about the power of copying formulas next time.

Link Me Up, About.com ***< (more stars is better)
www.businesssoft.about.com/computer/businesssoft/library/weekly
Karen Stille's example of relative vs. absolute in Excel

Learning Excel ****
s9000.furman.edu/DD/labs/Excel/
A course from Furman University that you can read online

Introduction to Quattro Pro for Windows *** www.stir.ac.uk/infoserv/advisory/QUATTRO/QUATTRO.HTML
Some information from the University of Stirling's Computer Advisory Service

Ask Mr Excel ***
www.mrexcel.com
A series of articles about using Excel.If the pages weren't so ugly, I'd give it 4 stars.

What Else?
For fun, you might ask yourself how you could make a multiplication table, going from 1x1 to 10x10, by using a single formula that you could copy to all 100 cells. It is possible, and I'll give you a hint: your formula will contain two Mixed Cell References.