Interface Seams

Rethinking Spreadsheets

December, 1997

Spreadsheets have traditionally been based on a rectangular grid. We'll look into simplifying formulas, and then at data structures other than a matrix.

Spreadsheets often have columns specified by alphabetic labels, and numbered rows. A specific cell can be identified by its coordinates, e.g., B17. Similarly, rectangles can be specified by their corners, e.g., A2:D14. Formulas are stored in cells in the table. The benefit of an electronic spreadsheet is that formula cells are automatically updated when the values they depend on are changed. A downside of the "formula in cell" approach is that the formulas themselves are mostly invisible.

We'll examine a number of alternative spreadsheet structures, with an eye toward keeping the formulas explicit and visible.

The simplest "spreadsheet" has no cells. The formula has no value to work with - so in effect it's a constant.

constant

The next simplest spreadsheet has one data value and one formula (which takes a single argument).

data value

formula

A list of items provides an interesting level of complexity:

value1

value2

value3

value4

formula over list

Commutative operators are particular useful: if
(a op b) op c = a op (b op c)
then we can drop the parentheses and treat the operator as an n-ary operator. Such operators include addition and multiplication, but also "average". (This is reminiscent of the "reduce" operator in APL: take a list and an operator, and the formula is formed by inserting the operator between each value.) Our formulas can be very compact if they're just describing one of these expressions. For example, "+" may describe adding all values in a list.

Trees provide a way to structure formulas explicitly. The interior cells provide a place to "hang" the operators. We can treat each node as having a list of children (thus reducing it to the previous "list" example).

For example, we may want a sum of products:

		+ 38
	/	\|	\
* 2		* 30		* 6
/\ 1 2		/\ 5 6		/\ 3 2

Notice how the leaves of the tree are the data values, and the interior nodes have operators. The value at the interior node is the result of applying the operator to the list of children.

If we're willing to restrict our formulas somewhat, we can require that each node at a given level have the same operator. (The example tree has this property.) We can have a very compact formula that describes each level. For our example, "+*" describes a sum of products.

What can we bring back to the rectangular spreadsheet? The idea of a visible formula, working across a row or down a column.

	Test	Test 2	Score
Obs 1	1	2	5	10 *	27 + Overall
Obs 2	3	2	7	12 +
Obs 3	-1	0	6	5 +
	10 + Total

(We've set it up so the formula cells contain the resulting value, the operator that applies, and an optional label.) By letting operators "stretch" across the cells to which they apply, we've removed the need for specifying cell and rectangle coordinates.

In summary, we've used a variety of alternative spreadsheets to explore some variations, and brought a new formula style to the rectangular spreadsheet. The new style retains the table structure and dynamic updating that makes spreadsheets so responsive, but also separates the data from the formulas in a way that will make their hidden contents more explicit.