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. ]