                         Cruncher

      Terence Simmons presents a spreadsheet program
       for the Spectrum with many important features
          including individual cells and windows

Number Cruncher is a spreadsheet program for the 48K
Spectrum with the following features:
* Labels and formulae may be entered into a matrix up to 26
rows by 24 columns and referenced to one another, thus
enabling scientific, financial, mathematical and other
tables or complex models to be generated.
* Individual "Cells" are selected for data input by moving
a cursor about the screen. Numbers and formulae may be
changed at will on the spreadsheet, which can then be
rapidly recalculated; this enables all kinds of "What if"
situations to be evaluated.
* The program utilises Paul Rhodes' "Character Print"
routine - YC, October 1984 - to give about 50 characters
per line and enable a 6 column by 20 row "window" on the
matrix, in which the labels, and values relating to
formulae entered, are shown.
* All Spectrum maths and logical functions are supported.
The contents of cells may be edited similarly to Basic
program lines.
* Formulae may be replicated relatively or absolutely.
Columns and rows may be inserted or deleted and calculation
references in the cells moved are automatically adjusted to
maintain the spreadsheet integrity.
* The spreadsheet may be recalculated repeatedly to enable
iterative solutions.
* Titles may be set.
* A hard copy of the entire spreadsheet is obtained on a
ZX-compatible printer, by printing the columns out side-
ways.

  The program [...will...] present a Menu of available
commands which are discussed below. Pressing any key then
presents a blank spreadsheet with two axes; the vertical
one shows a series of alphabetical characters referring to
the Rows, while the numbers on the horizontal axis desig-
nate the first six columns.
  The green cursor in the top left-hand corner relates to
cell A(1). The cursor may be moved about the screen to
refer to different cells using Caps Shift 5, 6, 7 and 8.
  If you try to go off the screen you will be prompted:-
Jump: No. of Columns - or Rows, depending whether you were
moving horizontally or vertically. Enter the number of
lines along or down the spreadsheet you wish the window to
be shifted, the axis range will then change to reflect your
response.
  To Input into the spreadsheet, press ! to enter a value
or formula, and " to enter a descriptive Label. The string
entered, or its value, will appear in the spreadsheet at
the cursor position; the contents of the current cell will
be reflected in the blue band at the bottom of the window.
For example, suppose cell A(1) contains the value 12 and we
want to calculate 25 percent of this and put the result in
A(2). Move the cursor across one column and enter A(1)*.25
  The value 4 will appear in the spreadsheet at the cursor
position, while the formula is printed in the Cell Contents
line.
  A formula in a particular cell can be up to 29 characters
long; longer expressions may be handled by splitting them
up. The value or label will be displayed in the spreadsheet
up to eight or nine characters long, scientific notation
being used to display greater values.
  The contents of the Current Cell may be edited by press-
ing Caps Shift 1. The relevant string will then appear at
the bottom of the screen and may be altered using the
left-right cursor and Delete keys. Note that Extended Mode
functions such as Sin may not be edited this way, the
string may have to be re-entered.

  Commands are accessed by pressing the / symbol (S Shift
V) - instead of the ! or " keys - followed by the character
specific to the function required.
@ - RECALCULATE - Recalculates the Spreadsheet row by row.
This is used when a value or formula is altered.
$ - DECIMAL FORMAT - Specifies the number of decimal places
displayed. Defaults to two.
a - REPEATED CALCULATION - This allows problems requiring
iterative calculations to be solved. The number of the
current calculation sequence is printed at the bottom of
the screen. The iterative process is halted by pressing the
Space bar when the problem has converged to the required
degree of accurace (keep the bar pressed until the current
calculation sequence is complete). As an example of the use
of this command, the spreadsheet [below] (fig. 1) had to be
iterated by adjusting the value of I(2) until the two
values in N(2) and O(2) were identical within acceptable
limits. This was achieved by adding the difference between
N(2) and O(2) to I(2) with each loop, i.e. the formula in
I(2) was set at I(2)+ -0(2)7. [Sic; the intent was probably
I(2)+N(2)-O(2), but how they got from that to what was in
the magazine...] The iteration procedure was halted manual-
ly when the difference printed in P(2) was less than 1.
b - BLANK CELL - Clears the current Cell.
c - CONCEAL CELL - Clears the cell on the Spreadsheet but
retains the formula/value in memory. This is useful for
storing intermediate steps in calculations.
d - DELETE - Deletes the Row or Column in which the Cursor
rests. When called the routine prompts "Delete Row or
Column?" - respond with an r or c. The line will then be
deleted and the following rows or columns will be closed up
to fill the gaps. Formulae moved in this way are altered
relatively so that their values are unchanged.
  The amount of string-searching and altering required by
this routine is such that several minutes may be required
for its completion, depending on the Spreadsheet size, so
be patient!
i - INSERT - opposite of DELETE; following rows or columns
are spaced out to leave room for the blank line inserted -
formulae moved are altered relatively to leave their values
unchanged. Like Delete, this routine can take a few minutes
to complete.
j - LOAD - Loads a Data file into the Spreadsheet. The pro-
gram asks for the number of rows and columns in the array
to shorten the time required to calculate the Spreadsheet
once the data has loaded.
m - MENU - Calls up the Help Screen of Commands. This can
also be useful for returning the Spreadsheet screen to
normal if display problems have occured; i.e. as happens
sometimes when Extended Mode functions are used in a
formula.
p - PRINT - Prints out the full Spreadsheet sideways on a
ZX-compatible printer (see Fig 1).
r - REPLICATE - Reproduces the contents of the Current Cell
along a Row or down a Column. The program will prompt:
"Replicate Hor or Vert?" to which you should respond with
"h" to copy along a Row or "v" to copy down a Column. If
the Cell to be copied contains a formula with references to
other cells, you will then be asked "Absolute or Relative?"
If the response "a" is given, the formula will be repeated
exactly; otherwise by replying "r" the equation will be
changed relatively each time it is repeated - e.g. A(1) in
a formula replicated downwards would become B(1), C(1),
etc., while horizontal replication would result in A(2),
A(3) and so on.
  Finally, you will be prompted "Range?" which is asking
the number of Rows or Columns to be reproduced. The bottom
right-hand corner of the screen displays the coordinate
number of the bottom right hand of the Spreadsheet - this
changes when the Sheet gets larger following the use of
Replicate or Insert.
s - SAVE - Saves the contents of the Spreadsheet on tape
as a data array under a filename as specified by the user.
t - TITLES - Sets the areas of the screen above and to the
left of the Cursor as horizontal and/or vertical descrip-
tive Titles. This area becomes highlighted by a blue back-
ground and cannot be changed using the Cursor. The Titles
remain on screen as you Jump from window to window. To
cancel Titles press "/" and the Space Bar. (Titles which
would be corrupted by Insert or Delete are automatically
cancelled when these Commands are used).
x - CLEAR SPREADSHEET - Clears the screen and arrays and
sets the Cursor at A(1). The user is asked for Confirmation
first.

  Those who wish to add their own commands or functions
will find this information useful. The Spreadsheet data is
held in two arrays: d$(lo,co,31) holds all the formulae,
labels and uncalculated values, while s$(lo,co,8) holds the
screen display. Variables A(1 to 24) . . . Z(1 to 24) are
set up to hold the value of each Cell.
Lines
  40- 120 Cursor movement loop.
 300- 320 Recalculation subroutine.
 350- 380 Iteration loop.
 400- 580 Insert routines.
 705- 880 Delete routines.
2000-3995 Edit routine.
4000-4040 Calculation routine.
5000-5095 Window Jump routines.
5100-5150 Print Screen.
6000-6060 Replicate routines.
6820-6840 LPrint routine.
6910-6940 Data load routine.
7000-7120 Input routine.
7300-7400 Command select.
7900-8020 Initialise/Menu.
Important variables are:
c, l  Cursor position on screen (hor, vert)
co,lo Cell position in Spreadsheet
cl,ll Current size of Spreadsheet
th,tv Title widths (hor, vert)
u, v  Number of current window (hor, vert)
  If the program terminates with an error message or if you
Break, restart using GO TO 20.

[Now for the figures. There were two. One was a screenshot
of the spreadsheet referred to under "repeated calculation"
above; the other was a print-out of the same spreadsheet at
a later phase of calculation. The first was not labeled,
the second was "Figure 1". On the other hand, the article
refers to cell numbers, which are not printed, and thus
only shown in the unlabeled figure. All in all, both are
useful for understanding the explanation; but neither is
well represented in an ASCII text file. Therefore, I've
extracted them as "Cruncher 1.png" and "Cruncher 2.png".]

[ And finally, a bit about the TZX. Naturally, this starts
  with the program and machine code, which also contains
  both the sideways printing character set and the screen
  proportional charset. Unfortunately, as printed in the
  magazine, the last one had a glitch at the end which
  meant that the final characters could not be used. This
  would not have been a grave problem - the only conse-
  quence would be that characters {|} and  could not be
  used in spreadsheets - but as it was only the character
  set which was buggy, not the machine code, it was easy
  to fix. The version of the code which is loaded is,
  therefore, a fixed one. Just in case, though, I've also
  provided the original code. Also on the TZX are the
  programs used to create these code files. The "corrected"
  program does give a checksum error, but since this only
  happens on the final line, the code produced can still
  be used.

  After these, I've added a few sample spreadsheets. The
  first of these is called "chess", and demonstrates the
  number-crunching power of the program. As the article
  says, you'll be asked for its depth and width; in this
  case, those are 20 and 8. This sheet also demonstrates
  some of the limitations of the program. First, as shown
  by the top lines, long texts will have to be split over
  several cells. Second, on line G you will find some sub-
  totals; these are necessary, because each formula can be
  at most 30 characters long, and there is no "total"
  function. The "Conceal" command came in useful here.
  You'll also note that the total, when you load it, is
  wrong. This is partly because of those sub-totals, and
  partly because of the way the program runs through the
  sheet. Re-calculate (/@) the data three times, and you
  will see it stabilise at the correct total. (Note also
  that "more than the king had" is a bit of an understate-
  ment - it's several orders of magnitude more than the
  entire Earth produces even in modern times! But that
  would not have fit on the screen without scrolling...)

  The second sample, "zoo", is more conventional. It's a
  cost-benefit spreadsheet for a fictional business, the
  Speccy Zoo. It's the full 26 rows high, and 18 columns
  wide - quite large. The zoo has three animals: turtles
  barely breed or grow old, but hibernate (so cost less in
  winter, when people don't use Logo), mice breed twice a
  year but die off just as quickly, and llamas just amble
  along peacefully and provide secondary income from sales
  to llama shepherds. Keepers are needed all year, park
  staff mainly in the busy summer; normal visitors (parents
  with children) mainly come during summer, concessions
  (pensioners) more during the off season.
  Salaries and ticket prices are fixed for the whole year,
  but can be changed in row D. Ditto for the selling price
  of a llama calf, and the monthly cost overhead. Seasonal
  and yearly averages (for numbers and individual costs)
  and totals (for totals!) are also given. An exercise for
  the reader would be to add totals in the other direction,
  to check for rounding errors.
  First point of interest in this one: the titles. If you
  put the cursor on E2 and press /T, you can browse the
  whole sheet while keeping the months and seasons, and the
  categories, on screen. Or you could start at D2, so as to
  keep the fixed numbers accessible. Also check out the use
  of lower case letters in the formulae in rows P, Q, U
  and W. This is a cheat. The relative /Replicate function
  only takes capital letters into account, but lower case
  letters work fine for calculation. Therefore, this is a
  workaround to create "absolute" references in a cell
  which is to be replicated relatively.

  The last sample is called "roots". It's relatively small,
  at only 14 rows by 5 columns. It calculates roots using
  Newton's method. For the square root this is, of course,
  superfluous - we could use the Spectrum SQR function,
  even within Cruncher. Even for the cube root, there is a
  simpler method using EXP and LN. As a demonstration of
  Cruncher's /Autocalculate feature, however, it will do
  quite nicely. Its use is very simple: just enter a new
  value into square B4 (729 will do for a start), press /A,
  and wait for the estimates to converge to the right
  values. For extra numerical enjoyment, use /$ to set the
  number of decimals to 6 or more. Then try finding the
  roots of 1.771561.
  This sheet, like the first, uses a few /Concealed cells.
  Try and figure out how they work, and why. They could
  have been left out, but then using this sheet would have
  been much more cumbersome. Take note, when you walk over
  some of them, that the display is corrupted. The reason
  was alluded to above under the /Menu command: Sinclair
  Basic functions take up only one character but several
  spaces, which messes with the value display on the bottom
  line.
  Note also that this root finder does not work for nega-
  tive values, not even for the cube root. This has more
  fundamental reasons, partly to do with Newton's algorithm
  itself; fixing it would require more work than a simple
  demonstration is worth.

  Cruncher is not perfect. There are several bugs left in,
  a few of which have been mentioned above. Some of these
  would have been easier to fix than others, but I chose
  not to. As far as I can tell, they only affect the dis-
  play, anyway, and not the calculations. In other words,
  your results should be correct, even if sometimes oddly
  shown. Should someone decide to have a go, the one which
  prevents long label cells to be printed in their entirety
  is probably the easiest one to start with.
                                 Richard Bos, March 2012. ]