Staffan Truve
The use of spreadsheets for what-if analyses can be simplified by allowing cell values to be set by small sliders and displayed as micro-graphs. The sliders define a range in which a cell value will be varied, and the graphs illustrate how other cell values vary when the slider-cell has a value in that range. The resulting mode of operation emphasizes interaction between the system and the user in searching for a desirable value of a cell, and also illustrates the stability of that value by showing the value in a context around it.
Spreadsheet, dynamic queries, what-if analysis
Spreadsheet programs are, together with word processors, by far the most widely used computer programs
today. A spreadsheet is made up of cells, which contain text, numeric values, and formulas which describe
how a cell value is to be computed from the values of other (which are referenced through their name, given
as row and column index). A cell can either display its (computed) value or the formula which defines that
value.
One important use of spreadsheets is to perform what-if analyses, i.e. to investigate how the value of some
cells affects other cell values in the spreadsheet. A typical example (see Figure 1) would be trying to decide
the number of units to sell of a product in order to reach break-even. In early spreadsheets this analysis had
to be done by trial-and-error methods - manually changing the value of a cell and seeing what the resulting
values of other cells would be, and repeating this procedure until the required value for a certain cell was
found. Recently, "goal search capabilities" have been added [2], and now provide an automated search for
such values. In the example of Figure 1, we can ask for a value of B1 which will give B7 the value 0, and
the value 50 for B1 will be found.
However, a manual, interactive search is in many cases preferable, since it gives the user an intuitive
understanding of the relationship between the varying and dependent variables.This can for example help in
avoiding the choice of a solution close to a "catastrophic" point - e.g. as in Figure 2 where the solution is
close to a sharp decrease in profit (due to a step increase in fixed costs which is described by the conditional
formula in cell B3).
FIGURE 2. A spreadsheet with a hidden surprise.
In this article we demonstrate two simple extensions to the traditional spreadsheet model, which provide the
necessary means for such manual, dynamic analyses. We call these additions `slidercells' and `micrographs'.
A slidercell is simply a cell which can have its value changed by manipulation of a slider. When a cell is
activated as a slider, a range is chosen. This range defines the slidercell's value range. The current value of
the slider is displayed next to its thumbnail, to yield as compact a representation as possible - see Figure 3.
The reason we strive for compactness is that both sliders and graphs are ordinary cells, and we wish to be
able to view many cells at the same time - this is one of the key ideas behind spreadsheets.
A micrograph is a cell which instead of just displaying a value displays a tiny X-Y graph, where the X
dimension is the value of an activated slidercell that the micrograph depends on, and the X range is the
range of that slider. The Y dimension is the value of the micrograph for each X value, and its range is
defined by the range of that cell's value. The current X value is marked by a vertical line in the micrograph,
and the current Y value (i.e. the value of the cell) is also displayed in the graph - see Figure 3.
Note that from the graph shown in cell B7 in Figure 3 it is immediately obvious that there is a break-even
solution, but also that this is a very unstable solution! With the automatic solver, we got no such
information.
As computational models become more complex, the usefulness of micrographs increases, since the co-
variation of several cell values can be studied simultaneously, as one or several slidercells are being
manipulated. The intuition which can be gained from such explorations is particularly helpful in situations
where no optimal solution to a problem can be found, and the best possible solution must be chosen by a
human.
Wilde and Lewis [3] also describe how to extend the spreadsheet model with interactive graphics. The main
difference compared to the work presented here is that instead of having a graphical "shell" on top of a
spreadsheet model, we suggest simple graphical I/O primitives (sliders and graphs) which replace the
conventional, textual representation of a cell in the ordinary spreadsheet. We also emphasize the interactive
exploration of cell value dependencies, by introducing the new notion of a potential value range for a cell,
and its reflection in the micrographs of other cells.
Our initial idea of extending spreadsheets with interactive, graphical I/O devices was inspired by work on
dynamic queries [1]. It is interesting to note that whereas "traditional" dynamic queries focus entirely on the
exploration of "dead data" stored in conventional data bases, the methods presented here deal with
potentially infinite data sets, represented as a number of computational dependencies. Therefore, even if
spreadsheets might be the first area where these I/O devices are used, they should also prove useful if and
when the dynamic query model is extended to work with, for example, deductive data bases.
We feel the ideas reported here indicate new directions for the design of spreadsheet systems. Furthermore,
the modular design of modern spreadsheet programs makes it possible to extend existing programs with this
kind of functionality. Our next step is to extend some commercial spreadsheet system with the I/O devices
presented here.
Christopher Ahlberg persuaded me to write down these ideas, and commented helpfully on earlier drafts of
this paper.
[1] Ahlberg C., Williamson C., Shneiderman B., Dynamic Queries for Information Exploration: An
Implementation and Evaluation. Proceedings ACM CHI `92: Human Factor in Computer Systems, 1992,
pages 619-626.
Introduction
RELATED WORK
CONCLUSIONS AND FUTURE WORK
ACKNOWLEDGEMENTS
References
[2] Microsoft Excel 5.0, Users Manual, Microsoft Corporation, 1994.
[3] Wilde N., Lewis C., Spreadsheet-based interactive graphics: from prototype to tool, Proceedings
ACM CHI `90, 1990, pages 153-159.
FIGURE 3. A spreadsheet with cell B1 used as a slider cell and cell B7 viewed
as a micrograph
![FIGURE 3. A spreadsheet with cell B1 used as a slider cell and cell B7 viewed as a micrograph](3D"st_fg3.gif"){kind=link}