Beginners' guide to spreadsheets
Do cells and replication head you for a padded cell? All is
not lost, suss them out with DAVID BROWN
ONCE upon a time in the days of yore when personal computers
had only just been invented (this is around 1974), two men working
at NASA ... please note before going any further that this story
is apocryphal and may very well bear no relation to the truth
whatsoever ... two men working at NASA thought up a rather good
idea.
They realised that this idea could best be exploited using personal
computers. So, in the now familiar manner of capitalistic entrepreneurs,
they set out on their own in order to market their newly discovered
idea and thereby to make their fortune.
The idea came to fruition as Visicalc, the program renowned
for selling more Apple microcomputers than any other product and
which spawned the now highly acclaimed Visicorp Corporation.
Visicalc was the first spreadsheet program and it made them
their fortune. It was the embodiment of an idea which was so simple
and in the end so useful that the reason why nobody thought of
it before has never ceased to be a source of wonder.
To understand the principle behind any spreadsheet program it
is necessary to imagine a huge sheet of paper marked out into
squares by a large number of columns running vertically and a
large number of rows running horizontally.
Any one of these squares — normally known as cells, boxes or
slots — thus created can then be uniquely identified by giving
a reference to the column and to the row in which it lies.
Normally the columns are referred to by letters, the leftmost
column being denoted as column A, the one to the right of that
column B and so on all the way to Z.
After the twenty-sixth column they may be referred to by combinations
of letters such as AA, AB, AC, BA, BB, BC, etc. Rows are referred
to by numbers from row 1 at the top, followed by row 2 and row
3, and so on.
You would then refer to the cell in the top left hand corner
of your imaginary piece of paper as cell A1 and the one three
columns to the right and four rows down from this as cell D5.
A spreadsheet program simulates such a piece of paper internally
and allows one to interact with it, writing into the cells one
of three things - either a label (that is, just a piece of text),
a value (a straightforward number such as 103.45) or (and this
is where the whole power of the spreadsheet lies) a formula which
uses values in cells from other parts of the piece of paper.
As a very simple example of this, let us imagine that we wish
to compute a profits figure which is obtained by subtracting a
costs figure from a sales figure.
Into three cells underneath each other we would put three labels
— one for sales, one for costs and one for profits. These simply
serve to keep track of which figures mean what.
Next door to the sales label we would put a figure, such as
200. Similarly, beside the costs label we would put a figure,
say 95.
Underneath the costs figure and next door to the profits label
we would put a formula. This might be +B1-B2, which would mean:
"Take the value found in cell Bl and subtract from it the
value found in the cell B2".
Assuming that these two cells hold the corresponding figures
for sales and costs the result of this computation would be 105.
This is the number that would then be displayed when the sheet
of paper is viewed. It might look like this:
It should be stressed here that there are therefore two pieces
of information concerned with a cell which holds a formula. The
first of these is the formula itself - in the case described this
is +B1-B2. The second is the number which is the result of evaluating
this formula.
When looking at a spreadsheet all numbers look the same, whether
they are values entered directly - that is, the raw data - or
the result of some calculations.
It is only by looking at the entry for a particular cell that
you can determine the origin of the number displayed in that cell.
As the example now stands we can see at a glance that the sales
were 200, the costs were 95 and that, not surprisingly, the profits
were 105.
If then we discover that our figure for costs was wrong and
it was, in fact, 108 not 95, we could simply change the value
corresponding to costs to the required value.
The value of the profits cell would then automatically change
from 105 to 92.
Let us extend the principle of this example and imagine that
these three figures are, in fact, those for a particular month
and that, in their turn, they affect quarterly and yearly figures.
It is not difficult to see that a change to one figure which
was wrongly copied might have extremely irritating repercussions
if the calculations were being done by hand.
All totals which depended on that figure would have to be laboriously
re-calculated using a calculator, a rubber and a distinctly unimpressed
human.
Spreadsheets have found their use in two major areas.
The first is in doing calculations which involve large numbers
of figures which need to be tabulated, added, subtracted, totalled
and generally mutilated in other varied and interesting numerical
ways.
The second area in which they have been found to be extremely
useful is in playing "What if . . .?" games.
Spreadsheet layouts can easily be used to answer questions such
as: "What is my total payment on a TV set if I enter a hire
purchase agreement with a down payment of £200, at an interest
rate of 15 per cent over three years, and what if I were only
to make a down payment of £150 . . .?"
Financial managers have spent many happy hours using spreadsheets
as tools to answer more difficult questions along the same lines.
In order for a program to be considered a spreadsheet it must
exhibit a number of basic abilities. First it must enable the
user to view a section of the sheet on the screen.
Moving around the screen, inspecting and updating cells, erasing
their contents, moving directly to particular cells - all these
are functions which are used constantly and must therefore be
simple and uncomplicated.
No hunting for the right function keys, changing direction before
moving, and so on.
Replication is another primary function. This enables a formula
to be repeated many times along a row or a column.
For example, this means that a formula which adds up the column
of figures above it and then multiplies the result of this by
a constant factor need not be entered more than once despite the
fact that it may be needed 12 times.
Replication is used a great deal, even in simple sheets, and
it must be powerful and at the same time easy to use.
It is imperative that the sheet has some reasonable number crunching
facilities as well as the normal arithmetic functions.
A spreadsheet program would be pretty useless without some ability
to provide totals, averages, maximums, minimums, statistical and
circular functions.
It is perhaps unnecessary to point out that such facilities
would be useless without both the ability to save and restore
sheets from tape or disc and also to take a hard copy by printing
out some or all of the sheet.
With this basic repertoire the spreadsheet would have the same
power as a good programmable calculator, with all the advantages
of a screen display and backing store.
However since Visicalc was first produced there has been an
ever-increasing demand for spreadsheet programs to have more and
more facilities for doing more and more spectacular and more complicated
things.
It is not unreasonable to expect a spreadsheet program to provide
a little more than the basics. Some of the nicer features might
include the following:
• Facility for editing entries (to prevent the need for re-typing
large formulas).
• Conditional statement, allowing for a variety of calculations
to be done depending on data entered.
• Ability to insert, delete and move rows and columns automatically.
• Facility for moving the cursor to an adjacent cell after entering
a piece of data (useful when entering large columns of figures).
• Facility to specify the format of the data displayed in a
cell (whether text is left or right justified, how many decimal
places to be displayed, etc) both for the whole sheet and for
an individual cell.
• Underline facility.
• Facility for splitting the screen into a number of separate
"windows", so that one can simultaneously view different
parts of the sheet.
• Variable column width applicable both throughout the sheet
and to individual columns.
• Choice of whether re-calculation of the whole sheet is done
whenever a piece of data is entered, or only when specifically
requested.
• Choice as to the direction of recalculation — which cells
are worked out before which others.
• Facility for reading and writing individual cell entries from
random access files, enabling sheets to be set up as templates
for different data which can then be captured using, say Basic
programs.
• Facility for merging different sheets together.
• Facility for using the colour capabilities of the computer,
a feature which seems to be sadly lacking from a great many "serious"
programs.
A spreadsheet incorporating all these advanced facilities would
make a very powerful package indeed. However, it is rare to find
such a program. Designers of spreadsheet programs tend to emphasise
some points and not others.
It is also true to say more features add to the price of a package.
One tends to get what one pays for.
The choice of which spreadsheet program to buy must depend,
for the most part, on the uses to which it will be put.
Buying a program which has only a minimum of functions is probably
good enough for applications at home. Business applications, on
the other hand, will undoubtedly require more sophistication.
It is important to have a demonstration and a thorough perusal
of the documentation of any package before buying. If necessary
spend a long time actually reading the manuals before buying a
product.
Avoid buying a package from any retail outlet which is unwilling
to let you play with a product before buying it.
My preference is to go for programs supplied in ROM. Not only
are they always on hand when needed but, particularly for tape
users, the hassle of loading a program can be more bother than
it is worth.
It also seems a shame to waste valuable RAM space on intransient
programs when Basic is lying idle.
Does anyone else find it strange that there are so few packages
available in ROM? — I mean why on earth is the language Lisp being
sold on tape?
It is worth pausing briefly to consider the future of the BBC
Micro as a machine for serious applications such as spreadsheets
and relational database management systems.
Packages of this kind produced so far have not been of a very
high standard, particularly when compared to those available on
CP/M machines of similar price to the BBC Micro.
It is a shame that Acornsoft, the company with the most credibility
among software buyers, does not encourage the production of high
quality applications programs by approving and commissioning software
more readily than it does.
If the British Broadcasting Corporation's micro is to survive
in the business market, software of a much higher standard than
that which has emerged so far will have to be produced very soon.
