AnalytiCalc Reference Manual Page 27 Variable Names A valid variable name might be H14 or N22 or A3, or the position independent forms P#n#m and D#n#m where n and m are offsets in Physi- cal or Display sheets from the current column and row. A valid range is 2 variable names separated by colon (:). Thus the range B2:B12 is valid and means B2 through B12 inclusive. The range expression D#0#1:D#0#12 is also valid and means the cells on the display sheet in the same column but 1 to 12 rows down from the current cell in- clusive. (The displacements may be negative too, with - sign to in- dicate it, as D#-3#2 for the display cell 3 columns left and 2 rows down from the current position.) The P# and D# forms are valuable since they work wherever you move them. Other variables may be relo- cated when moved (renaming them to correspond to their new loca- tions), but the P# and D# forms need never change and allow working with projections across the physical sheet in various ways. There are a few restrictions with these forms. They usually need to be en- closed in parentheses (e.g. (P#3#5) rather than P#3#5) inside ex- pressions. For most purposes they do work interchangeably with other name schemes however, though the "normal" naming scheme is generally easier to use. Copying with relocation works with normal cell names, provided that the cursor is above and left of cells to be renamed (see below). The keypad copy command normally moves to A1 during a copy for this reason. The D# forms do not always work in the ex- tended address areas of the sheet, depending on recalculation mode. The special construction P#%ab or D#%ab allows relative address- ing relative to current physical or display location using accumula- tors named a or b (where a or b may be any accumulator name from A through Z but upper case is REQUIRED). This computable relative ad- dressing affords greater flexibility in command procedure construc- tion. If a formula contains the construction {VN (where VN is any cell name), the FORMULA for cell VN is inserted into the formula being evaluated at that point, when the cell value is to be computed. This may be done several levels deep, but is stopped after 10 translations to avoid infinite loops due to circular references. Some care must be used here since the final translated formula must fit in 110 characters and will be truncated if it gets too long. This construct is useful where formulas are to be tested. These cell names may (in AnalytiCalc versions for 320K and over) be replaced by alphanumeric names of your choice; this will be discussed below. The formula replacement feature provides a handy inline macro capability permit- ting complex formulas to be entered more easily. This feature may be used with the P#%ab form of addressing to provide runtime addressing of formulas anywhere in the matrix. It may also be used (with the position relative P## forms of cell names) to conserve storage where a long formula must be repeated many times, at the cost of some speed. AnalytiCalc Reference Manual Page 28 The command processor may use these inline macros in commands. If a command line contains 2 successive underscores (__) anywhere in its length, the underscores will be stripped out of the command line and the command line will be scanned for {Vn forms. If any are found, they will be substituted for prior to interpretation of the command. This will occur AFTER any console text is read if console text is required by one of the %%% forms of command line. This per- mits formula cells to be used as a convenient place to store inter- mediate answers in models being driven by command files. It is not particularly useful from the console, but works, and may be used to reenter a formula without moving it explicitly if there is a reason to do so. (ENTER __{Vn). Since it is sometimes useful to have mixed formulas which allow certain cell references to be absolute and never relocated, with other references which are relocatable, cell names may be specified absolute. If the character "$" (dollar sign) is placed after the letter, the number, or both, parts of the variable name, the part of the variable name followed by the dollar sign is absolute. Thus, the variable AB30 is relocatable in both row and column. Variable AB$30 is relocatable in row only (the 30 is not followed by $), and the AB column will not be changed in any relocate. Variable AB30$ is relo- catable in column, but the row 30 reference does not change. Vari- able AB$30$ is totally absolute. Note that these absolute variables are harder to move around even on adding or deleting rows, so they should be used with care. This relocation protection can be tem- porarily inhibited with the OV + command and reinstated with the OV - command. This will allow copy of cells with relocation if it is needed, without laborious editing. The program starts in OV - mode. If the OV + mode is set, the program will remain in OV + mode until OV - is set up again. The commands may be given in the form OV-, OV -, OV+, or OV +. Variable Ranges Variable ranges must lie in a single row or column. Erroneous specifications are rejected and ignored generally. For versions of AnalytiCalc supporting matrix operations, matrix dimensions are however given by specifying the upper left and lower right corners of the matrices. Thus the range A1:B2 is the 2 by 2 matrix containing cells A1, A2, B1, and B2. For operations using full rectangular re- gions, support is offered via auxiliary keypad commands which allow filling of large regions or moving data into them. This is done via command files automatically invoked, but the methods used are tran- sparent to the user. Other operations on rectangular regions not confined to a single row or column may easily be added in the same way. The internal implementation permits copying rows to columns and vice versa as well as rows to rows and columns to columns; this is why regions must be one or the other. It is expected that regions are specified starting at top left and ending at bottom right. This AnalytiCalc Reference Manual Page 29 must be done; regions will not be completely handled otherwise. By using in-cell loops for their innermost computations, these macros are quite quick in execution and offer few disadvantages. They do however ask separately for the bounds of the areas being modified. Function Arguments Note that the square brackets ([]) are needed to delimit arguments in multiple argument functions. The results are left in accumulator % and in the cell. The IF form will compare 2 variables and execute either the statement or the else-statement (which must NOT be another IF). The valid relations are: .EQ. Equal .NE. Not Equal .GT. Greater than (V1 greater than V2) .LT. Less than (V1 less than V2) .GE. Greater than or Equal to (V1 >= V2) .LE. Less than or Equal to (V1 =< V2) and must appear in this form. (Note that your formulas must generally be in upper case only; text can be anything.) Unary Functions The second class of formulas permit fully general arguments of expressions, cells, and the like (and are evaluated AFTER the multi argument functions lexically). These are more conventional, use parentheses, and include single argument functions like SQRT, ALOG, EXP, SIN, COS, AINT, ABS, ATAN, and so on. See the built in interac- tive calculator mode manual section below for more detail. However, they may use variable names as anywhere, not just single letter ac- cumulators. Thus, SQRT(A5) is a valid number. There is a function list in the documentation of Calc mode later in this manual. Accumulators (Scratch Storage) The single letter accumulators of Calc mode (letters A through Z and %) are available globally and may take on all Calc mode data types (see the description of calc mode below) including multiple precision. They are usable globally on the entire sheet and may serve as arguments to command files or return cells for results. In- side command files, Calc mode can perform any of its usual calcula- tions. The multi-argument functions listed above are not available there (though they can be simulated by enumerating cells), but every- thing else is. The letter accumulators are available in all cases in addition to cells. Of course, any unused cell may be used as an ac- cumulator too, but cells in the spreadsheet are at most 8 bytes long and cannot hold multiple precision arguments. AnalytiCalc Reference Manual Page 30 To get, e.g., the square root of the sum of several variables (in a row or column; ranges may only be in rows or columns together) one might have a formula line like: SUM[A1:A20,B4,F2,C5:C20,J3:M3]\SQRT(%) which computes the sum of the 2 cells and 3 ranges and leaves it in %, then computes the square root of that and leaves it in the cell. The formulas to Calc may be complex but MUST NOT be longer than 78 characters. Break up longer ones so they can be fed to Calc in smaller chunks. Notice that the expression given could have been written as SQRT(SUM[A1:A20,B4,F2,C5:C20,J3:M3]) but was broken up for tutorial purposes. Auxiliary Commands and Functions The commands of CALC mode should be examined carefully for usefulness. You can enter programs into CALC mode which can be used as user-defined functions from the sheet if need be. See the do- cumentation about the Calc mode (interactive calculator) for full lists of functions and further information. Several commands available in cell calculations which affect computation exist; several will be described here as well as in the section on calc mode (which describes single cell calculation facili- ties in addition to those described here). It should be noted that these functions start with the * character which also starts comments in command files to AnalytiCalc. Because of this, the "Enter-mostly" mode treats any text that starts with * as a comment. To enter a FORMULA beginning with an * character in "Enter-mostly" mode, you must use the syntax /EV formula where formula might be something like "*U IDATE" (without the quotes), i.e., something that may start with a * character. This method allows the formula to be entered correctly. Notice this is the "Command-mostly" command for entering a formula. It ALWAYS works, regardless of the type of formula. One of these is the *G command. Its syntax is *G V1,V2 which has the effect of evaluating variables V1 and V2 as column and row numbers on the physical sheet and returning the physical sheet variable addressed by those variables. The ranges are clamped to fit on the sheet. This gives indirect addressing, permitting lookup tables to be made up by creating the index you want and then AnalytiCalc Reference Manual Page 31 accessing the addressed table element. Typically the *G command would be the last on a formula line, though it need not be. Its value is returned in the % accumulator. Another is the *W command, which simply takes the number in % and places it in the formula for the current cell on the spread sheet. This may be used to "freeze" a value, replacing a formula by a constant for future use. Note that the previous contents of the formula entry are lost. Thus, a *W command in the sheet will nor- mally cause itself to be overwritten after one use. The number is stored in a format D35.24 in the worksheet. To implement user functions, if Calc sees an expression of the form "*@filename.ext", it will execute that file as a command at that cell. Any filename may be used and as many functions as desired can be handled in this way (with obvious overhead), with nesting depth of up to 3 permitted. These command files correspond to "macros" in other spreadsheets, and exist outside the sheet, so a command file can be used by many worksheets. AnalytiCalc has also a command called *P which allows resetting of the physical coordinates while executing a cell's formulas, FOR THE DURATION OF THAT CELL'S FORMULA ONLY or while in K mode (calc mode) only. The syntax of the *P command is either just *P (which causes AnalytiCalc to prompt for column and row), or *P Variablename (which moves the current location to the named location), or *P@ variable1,variable2 (which moves to the location which is defined by variable 1 and 2 as col. and row). There are several other useful calculation commands, a few of which are summarized here. The command *F LABEL will evaluate the % variable. If it is positive and nonzero, then the current input line will be rewound and the program will (silently) seek a line of the form *CLABEL. This allows loops to be defined with % as the counter. This command acts on the unit used by the AnalytiCalc @ command, NOT the unit for the Calc *@ command, and is a way that the Calc formula conditionals may be reflected back into its command processing. The TE expression command should be used first to set the % variable as needed to any arithmetic expres- sion to control the loop. The command *J LABEL will act as does *F LABEL but its search will be in the current file opened by a Calc *@ command (i.e., an *@file inside a cell on the sheet, or given from the console after a K command [see below] to enter bare interactive Calc). AnalytiCalc Reference Manual Page 32 The command *QF or *QW file ?searchkey? ("Query Float" for getting a real ("floating point") number into the sheet from a file, or "Query Write" to write part of a file into the sheet) will open file and look for lines starting with searchkey as given (with _ as a wild character). Upon finding this, it will look for the and if it finds it, it will select the part of the line in the file between the 2 characters given (which may be alike) as they are first found, and will do one of 2 things. If the command was *QF, then Calc will Float the value found there (attempting to use a large D format) and return it in %. If the command was *QW, then Calc will Write the selected part of the record into the formula at the current physical position on the sheet (which may be reset by an IMMEDIATELY PRECEDING *P command). The record may contain the search key anywhere. It is up to the user to make sure the formulas copied make sense. This however allows access to sequential files of data which can be created by an editor, word processor, etc., and which might hold keyed information that the spreadsheet should be able to manipulate. As a user convenience, there may be a second key specified (also inside a pair of ?s). If two keys are specified, Calc will search for and require that BOTH be somewhere in the record accepted. Note too that the files opened may not have any records longer than 128 characters per line. There are 2 variant forms, in- volving the 4th character (i.e., the next one after the F or W of *QF or *QW). These may permit sequential access to a data file. The *QFK command is exactly like the *QF command (ditto *QWK and *QW) BUT it will not close the data file after finding the desired cell unless it reached EOF. This permits subsequent commands of the *QFN command (for "Next") to continue reading the file. The *QFN command will continue reading the file (the file spec must be there but is ig- nored) from the next record and will also not close the file. (Ditto *QWN.) The *QFC command will act exactly as *QFN BUT will close the file when the request completes. Thus, a file may be opened with a *QFK (or *QWK) command, subsequent data retrieved by *QFN commands, and finally the last data retrieved with a *QFC command. Also, *QF commands will close the file prior to opening another. The command mnemonics here may be read as Query File [Next/Close/Keepopen]. Formulas entered may have the following constructs which will be edited into the formula as entered (so a command file can enter them): _@V1,V2 Means get the values stored in V1 and V2 and use them as column and row locations pointing at some cell in the sheet. Replace the construct with the name of that cell. _#V1 Means take the real number in cell V1 and unpack it as if it had been a packed value from a formula with 8 characters packed; then convert it back into ASCII and place in the formula in place of this construct. This construct is intended to be used with the *U STRVL AnalytiCalc Reference Manual Page 33 function to allow retrieval and edit of formulas. The *U XQTCM function permits use of the EDit command (as well as others) within a cell for string manipulation. Only 8 characters at a time may be represented (the limit imposed by cell size), but repeated use allows handling any desired length. An application would be setting the cell used as V1 to a packed representation of either of two strings inside an IF [] and then EDit- ing the string into the formula as desired where one of the 2 strings should be made to appear. This capabil- ity is crude but provides the bare bones functionality. Additional USRFCT calls to give simpler (but more ver- bose) string manipulations are not too hard to add should the user need them. Formula Length Formulas have 109 characters allotted to them for use inside the spread sheet. It may happen these are not enough for use. In other spreadsheets, the technique is to put the formulas in other cells in- side the sheet and possibly use screen windows to avoid display of the intermediate results. While you can use that technique here too, you have an alterna- tive. It is to use an indirect file in that cell and inside the cell in the spreadsheet just have a command "*@FILE.TYP" where the file is named "FILE.TYP" (you pick the real name). Inside this file may be whatever calculations you like. So long as the file is to operate in a fixed cell location in your spreadsheet, you may use variable names like A3, D1, etc. in- side the file. Your command files may include any multiple argument functions except the IF function. (The *F and *J functions can be used to provide equivalent functionality inside command files however.) The only restriction on multiple argument functions is that their arguments must be cells, not general expressions. Once you add or delete rows or columns, or copy a formula, though, the file may not work as expected if the cell calling it has moved. Since the relocation done by AnalytiCalc only operates on formulas stored directly in the spreadsheet, it cannot relocate vari- ables in the indirect files. However, you can make the files "posi- tion independent" so they WILL work wherever they are called from. (This has the side benefit that a single file can be called from many cells.) The way to make the files position independent is to use location-relative variable names instead of absolute ones. That is, inside cell B4, the cell immediately above can be named B3 (the ABSOLUTE name), or it can be relocatably named P#0#-1 (the relative AnalytiCalc Reference Manual Page 34 name). Likewise the cell one to the right would be named either C3 or P#1#0, and so on. The cell two down and one left would be either A5 or P#-1#2 (you can begin to see how it goes). A file containing only location-relative variable names will work even if called from a different cell, since the references will be figured out when used. These references are more difficult to write than the absolute ones, but since they work without renaming, they provide a level of flexi- bility needed where long formulas must be used. These forms should be used wherever indirect files are used to hold cell formulas. The computed cell name forms like P#%ab can be used also provided the a and b accumulators are initialized properly, or the command file does its own relocation using the X and Y accumulators set on entry. By using command files, it is possible to add your own functions to the spreadsheet. Just devise a command file that performs the arithmetic and addressing you need and use *@file.typ where you want to evaluate the function. Arguments can be passed in accumulators and P## or D## forms can be used for cell addressing (note that it's P#col#row and D#col#row). The *U HERE function can be used to return the current location in two accumulators to allow address arithmetic. Linking Multiple Sheets To enhance AnalytiCalc, it is possible to have a cell refer to cells from another, saved sheet anywhere in the system. The sheet must normally have been saved NUMERICALLY (see PPN command below) starting at the upper left (i.e., cell A1) as would be the convention for using with ACGRAF, the associated graphics output utility. The command *XV filename V1 will eXtract the Value of cell V1 (V1 is any variable name, e.g. C15, AA3, F9, B34, etc.) from the saved sheet saved in filename. The filename is any legal host system file specifier. Note that there may be as many files as desired here, but only one cell is returned. If it is desired to extract a formula from a saved sheet saved NON-NUMERICALLY, the command form is *XF filename V1 and this will cause the formula in "filename"'s cell V1 to overwrite the current physical sheet. To avoid this, one might write a formula like *P V2 \*XF myfile.typ V1 which would cause current sheet variable V2 to have its' formula loaded from saved sheet "myfile.typ"'s variable V1. Note too that the GPR or GDR commands will load saved sheets onto the current sheet (starting at the cursor) with all formulas relocated, so that entire saved sheets can be merged quickly where the one by one operations just described would be too slow. AnalytiCalc Reference Manual Page 35 In general, save your sheets symbolically (non-numerically) for archival purposes; save numerically for post processing or handling of data by "master" sheets or for graphics. You may want to edit AUXKPD.TXT so that F4 saves the entire sheet both symbolically and numerically if you frequently do both and want the two to be in syn- chronism. The default save, PPX, now saves both numbers and values so it is normally all that is needed. Loading Non-AnalytiCalc text files onto the Sheet FILE LOADING F filename/nnnn Read filename, skipping nnnn lines if the "/nnnn" is there, and load contents onto the currently displayed screen, broken into columns as the current screen is set up. This command is designed to make it easy to load document files into AnalytiCalc in quasi-image mode. Once loaded, the characters are in formula cells and may be moved around the sheet. The display format must be reset to get them to be treated as non-text; this command will reset all loaded cells to text format display. It repaints the screen upon loading. This feature makes it easy to cap- ture data in AnalytiCalc from other unrelated programs. You can com- pute with cells that are loaded in this way but contain valid numeric data by changing them to numeric cells by the DF range [*] command. If you need to add decimals to the end of the cells' numbers, the ED //./ command will add a decimal to the end of a number and the ED / // command can be used to get rid of extra space characters in a formula. AnalytiCalc Reference Manual Page 36 Getting Saved Sheet Data from Files RESTORING YOUR SPREADSHEET GD Reload sheet down/right onto display coordinates. GP Reload sheet down/right onto physical coordinates. GPR Reload sheet onto physical coords and Relocate formulas as if the saved A1 cell were moved to the current cursor. GDR Similar to GPR but on display coordinates. Gxx+ Add saved sheet (if default save) values to current values; enters Recalc Manual mode. Gxx- Subtract saved sheet (if default save) values from current values. Also enters Recalc Manual mode. The GP/GD commands are the inverse of the PP/PD commands and load those files to potentially new locations. This is the simple way to merge sheets. In these cases, you are separately asked for filenames and limits on what part of the sheets to load/save. Only cells that are marked in use are saved, so sparse sheets take less space than saving the direct access files. Note that on save or re- store the ranges to restore are asked for. To restore the sheet starting at the cursor, specify the lowest row/column offset of 1 and the highest either 0 or a very large number. Any rectangular region can be restored by giving the X and Y coordinates of the lowest and highest offsets from the upper left to restore, counting from 1. If the P or D is omitted from the GPx or GDx commands (where x is either R or anything else), the action will be determined by whether the sheet was saved by a PP or by a PDx command. REMEMBER: THE LOW OFFSET SHOULD BE 1 OR LARGER, NOT 0. The GPR or GDR commands relocate a saved sheet to a new loca- tion. If for example a sheet is saved from A1 on down and right, if it is loaded into another sheet when the cursor is at another cell, all formulas are relocated on load so that they are correct for the new location. (This follows the normal relocation control rules so that the $ forms of names may inhibit relocation.) The intent of this is that it makes it trivial to merge multiple sheets and make summary sheets. In AnalytiCalc/Amiga, the logic to write to cells involves checks of the built in symbol table to ensure that cells are not pre- viously there and needing to be replaced. This slows the Get opera- tion considerably, and is normally bypassed. However, where a saved sheet is being merged into an existing sheet (rather than restoring into a previously empty region of the sheet), the full logic may be AnalytiCalc Reference Manual Page 37 needed for correct operation. To get it, enter the filename followed by "/M" (so that to read SAVESHEET.PCC in the full-update mode you reply SAVESHEET.PCC/M to the "Enter Filename>" prompt, rather than just SAVESHEET.PCC, as an example.) (M stands for Merge.) The + or - modifiers in the 4th character of the command allow you to add or subtract multiple saved sheet values. These require that the normal default save (which includes both numeric and formula data) have been done to save the sheets being added. The values in the sheet have the saved values either added (if +) or subtracted (if -) from the current values. The manual recalculation flag is set to prevent any automatic recalculations also, since formulas are loaded from the last sheet loaded and overwrite other formulas in the same cells. (This is generally not a problem.) To manipulate these values if there is a need to, the TEst command and the matrix math functions (which include multiply matrix by constant) allow two dimensional areas to be moved around or multiplied by constants without turning on the sheet recalculation. Thus, averaging 4 sheets could be done by a GP command, three GPx+ commands, and then a TE using the *U MMPYC function to multiply the whole area by 0.25. (One could use TE A=0.25 to get a "cell" A0 to use in the MMPYC function if no 0.25 value existed to use already.) The AnalytiCalc HELP System HELP Hn Display a screen of help. (n from 0 thru 9) The H command will display a screenful of help giving a summary of many AnalytiCalc commands and syntax options. When you type a second return, it will redraw the screen. The F1 key also means 'Help', and the ALT-F1 key draws a diagram of the function key assignments. Control F1 will draw a one line reminder HELP message on line 25. There are multiple help pages. They are selected by commands H0 through H99, the number defaulting to 0 and selecting which help screen will appear. AnalytiCalc Reference Manual Page 38 MOVING TO LOCATION (GOTO anywhere) LOCATE CURSOR (GOTO somewhere on the sheet) L variable-name Position cursor at variable-name The L command moves the current position to the variable named on the Physical sheet (and the display sheet if the variable is on the display). Examples might be SKIP 1 L B3 Move to cell B3 L D6 Move to cell D6 Setting Direction for Motion after Entering Cell Contents MOTION DIRECTION (Set Default Direction to move after an Enter) THIS COMMAND SETS DIRECTIONS FOR AUTOMATIC MOTION AFTER ENTER ONLY. SEE BELOW FOR HOW TO MOVE THE CURSOR AT OTHER TIMES. M0 (Restore last auto move direction) M1 (Move up) M2 (Move down) M3 (Move left) M4 (Move right) M5 (No auto motion) The M command causes automatic cursor motion after an E command in the direction indicated. This only occurs after an E command. The default condition is not to move at all after an E command, and this may be reestablished by a command of form M5 (actually Mc, where c is any character other than 1 to 4.) The M0 command restores the last automatic motion direction and saves the current one as the new last direction. This is useful where a command procedure needs to turn off auto motion but wants to turn it back on at the end; a dummy EDit may be used if a single motion is needed.