Spreadsheet Basics

R.B. Nerf

(Notes to myself for presentation... May be elaborated later.)

Mental Model

The spreadsheet is the Swiss Army Knife of computer applications. It can do so many things that it seems like it's the first tool I try when I've got a quick-and-dirty problem to address with the computer. Because of the great variety of things that a spreadsheet can do, I'll try to develop some simple analogies to keep things from getting too confusing.

Cells and Values

Let's begin with one of those flat plastic boxes with a rectangular array of cells which are used for storing small bits of hardware, fishing tackle, or the like. In our analogy, the things that can be put into the cells are called values, which in our case are of two kinds: numeric and text. We put an item in a cell by clicking on a cell and typing; in some spreadsheets the value appears inside the cell as we type, in others our value is constructed in a field at the top of the spreadsheet and doesn't appear in the cell until we indicate that we're done with the entry (e.g. by clicking on a checkbox, hitting the enter key, etc.). Any value which can't be understood as a number is considered to be text.

Formatting

We can view the items inside the cells through the clear plastic lid; in our analogy, the lid can be adjusted to change the appearance (but not the value) of the items inside each well, i.e. it can format them. One of the more basic results of the formatter is that numbers will appear right-justified in the cell, text will be left-justified. Often, the first clue of a typo on data entry is an unexpected left/right justification of the value. In addition to the usual text formatting attributes (bold, italic, font, color, etc.), there are ones that operate only on numbers. Type 12345.6789 into a cell and experiment with various formatting options. Some, like placement of the decimal point, dollar signs, commas, etc. are self-explanatory. Others, like date and time are a little more complex. Formatted as a date 12345.6789 might appear as 10/19/37 (In Appleworks, the numeric value inside the cell corresponds to the number of days since 1/1/1904).

Formulas

There is another way to get values into our cells. In our analogy, we attach some plumbing to the back of some cells; the attached mechanism looks at the values in other cells that we designate and it then computes a value that it places into the cell to which it is attached. We attach the mechanism to a cell in a manner similar to how we put values into the cell; the principal difference is that we begin our entry with an equal-sign, =. The simplest way to demonstrate this is to click on an empty cell, type =, then click on a few other cells, then hit the enter-key. The algebraic-appearing text that appears in the input field is called a formula. The cells which are referred to by the formula are specified by addresses that give a horizontal location as one or more alphabetic characters and the vertical location by an integer. One can also enter a formula by typing it in, or by a combination of typing operators (e.g. *, /, -, etc.) and clicking on cells.

Footprint

If one is happy with entering a formula wherever a calculated value is wanted, then the story is as simple as described above. If, however, one wants to take advantage of copy/paste, or other operations that move a formula from its original position, then we must consider what I call the footprint of the formula.

We will begin with a semi-practical example, balancing a checkbook...

Relative References: Copy, Paste, DrawDown, etc.

A formula for calculating the balance is constructed and then drawn-down to fill the rest of the column.

Absolute References: Copy, Paste, DrawDown, etc.

To calculate how much sales tax was paid, an absolute reference is needed that behaves differently on draw-down.

Ranges

Summing up the sales tax demonstrates the use of ranges. (and functions)

Effect of Insertions or Deletions of Rows or Columns

Adding extra columns or rows can have unexpected consequences...

Iterative Refinement a/k/a Cut and Try

How can we come up with a factor that multiplies an expenditure to give the 8.6% tax that has been summed in?