AnalytiCalc Reference Manual Page 18 SORTING DISPLAY ROWS or COLUMNS SORTING DISPLAYS DSRA#nn Sort display on row/col nn (display coord) The DS command sorts "normally" ordered display sheets. It operates something like a sorted OA command, the sort key being the number in a column or row. The command looks like this: DS Command specifier R or C - Row or Column specifier (i.e. whether to sort a row or a col.) A or D - Ascending or Descending order sort nn - row or column number to sort on. The command letters must all appear together. Thus a command to sort Column 4 on the display in Descending order would be: DSCD 4 and a command to sort Row 3 in Ascending order is: DSRA 3 The sheet should be in "normal" order (as from an OA command) prior to the sort for use. The area sorted is the part of the sheet dis- played (set by the DB command, so it may actually exceed what shows on the screen. The Amiga version of AnalytiCalc normally supports very large display sheets to permit printout of large displays which are not all visible on the screen; the screen display discards all the un-displayable points, but the print routine, and the sorts, do not.) That is, the display sheet size may be given as larger than your screen will display, and the sort will cover that area. Note that ONLY display sheet locations are sorted. A sort on the PHYSICAL sheet may be done via a cell or sheet program, but this primitive does not. AnalytiCalc Reference Manual Page 19 DATA TYPE control DATA TYPE SETTING DT V1:V2 F Set V1 thru V2 to Floating data type DT V1:V2 I Set V1 thru V2 to Integer data type The DT command sets Float/Integer attributes on physical cells. Initial default is F (8 bytes) but this can set it to I (4 byte in- teger). Computation is done in floating point always, but I type variables will be converted to integer when stored and may be dis- played in valid Fortran formats. This information is stored/restored by the P/G class commands. The type flag may also be set with a DF command, which recognizes I formats as implying that data is of in- teger type (if the first character of the format is an I) and treat other formats as floating point (real) data. The integer data can hold up to + or - about 2 billion. The floating point representation permits nearly unlimited size numbers but only has 16 (roughly) di- gits precision. Integer arithmetic is, within its valid range, exact. When integer data is retrieved, it is converted to floating point for calculations and converted back upon storage; this avoids many potential internal problems of function validity. Setting Column Width DISPLAY WIDTH SETTING DW N,M Set column N on display to be M characters wide DW lets you set any column width. They are independent of each other, and may be set in any order. Maximum width supported is 132 columns. Note that when many widths are to be varied, the VM mode is useful to prevent redrawing the screen between settings; the V mode can then be reset once the widths are as desired. Setting column widths is expected to be frequently done via a command file (see the @ command below). The column number is normally displayed across the top of the screen along with the physical cell the display column currently is pointing to on the top row. This is shown in a form like "AF = 4" in reverse video (or different colors). You use the 4 in this command. Because some commands (notably this one and the DL command above) use DISPLAY coordinates, the columns are labelled in this way to make it easy. Rows are not labelled because the labels take too much space on screen. AnalytiCalc Reference Manual Page 20 EDIT command EDIT FORMULAS ED 'string1'string2' Replace string1 with string2 in current cell formula. Either string may be null if desired. The ED command allows editing of formulas with some extra bells and whistles. Basically, the new string replaces the old one, EXCEPT that it is possible to do some limited argument replacement. The constructs &1 through &4 will be replaced by arguments 1 through 4 as last entered by the "-" command (see below). These may be from 0 to 51 characters long each, and multiple & constructs may appear in a substitution. No & constructs may recurse, however. A second ED command can have the effect of the recursion in some cases however. The construct &5 will evaluate accumulator Z and, if it is in a range where it may be so represented, take its integer part and con- vert it to a numeric string (up to 10 characters) in the place of the &5. The construct &6 will replace the cell with the contents of the integer part of accumulator Z provided that that number is between 1 and 256. Thus, any single non-null ASCII code can be entered. Ac- cumulator Z is used to avoid conflicts with %, which is used too fre- quently to reserve for this purpose. To set up Z interactively to a constant, the command "TE Z=nn." will set Z to the value nn. The EDited formula is re-entered into the cell after the edit. If the initial string is null, the EDit command appends the replacement string onto the contents of the cell. If the replacement string is null, the corresponding contents of the cell's formula are deleted. Note that ALL matching strings in the formula are replaced, not just the first. If automatic motion has been specified after an Enter, it also occurs after an EDit. This allows a "dummy" EDit (e.g. ED / / /) to simply move in the default direction without changing any formulas. AnalytiCalc Reference Manual Page 21 ENTER (Entering Data and Expression Formats) E expression Enter expression and set as formula or text based on contents ET expression Enter expression and set as text E" expression Enter expression and set as text. Note this form does NOT translate to upper case. EV expression Enter expression and treat as computable. This command enters the given expression at your current cursor position, and may optionally move in some direction that has been set up by the M command (Move) after the entry is made. The Expression may be text or one or more numbers, functions, or equations (separated by the \ (backslash) character if more than one per cell) in fully parenthesized infix notation. The expressions en- tered resemble normal algebraic expressions, with the usual operator precedence. There are 26 global accumulators named A through Z (plus a special one named %) available; the result of a calculation is always left in the % accumulator as well as the cell. Operators available include addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (** or !), with normal precedence. Many functions are also available, described below, for other opera- tions. The operator evaluation order is functions, exponentiation, multiplication and division, addition and subtraction, just as in Fortran and most programming languages (and on most calculators). Parentheses may be used to group calculations to override these defaults. Simple examples of Enter are: E 523.458 Enter the number 523.458 into the current cell E SUM[A3:A15] Enter the formula to compute the sum of cells A3 through A15 and produce the result in the current cell E MONTHLY REVENUES Enter the label "MONTHLY REVENUES" into the current cell. Notice that the string does NOT have any of the characters .[(+_ in it, so it is treated by default as a label. E (A2+A4+A6+A8+A9*A10+A11*A12)/(SUM[A2,A4,A6,A8]-A13) Places the above formula into the cell AnalytiCalc Reference Manual Page 22 so that it will be evaluated. The multiplications and divisions are done before additions and subtractions, as is the normal mathematical standard. The formulas may of course contain references anywhere on the sheet; they do NOT have to all refer to the same row or column. The text entered becomes the Formula for that cell, and AnalytiCalc takes a guess as to whether that text is just text or a number to compute. If the text contains any of the characters (, +, -, ., or [, it is assumed to be a formula, and the cell is marked as containing a computable formula. Otherwise it is just treated as a label and the formula itself is displayed. If you find the guess is not to your liking, the DF command below lets you alter the format and tell AnalytiCalc that the entry is / is not a numeric. An addi- tional special character is the '"' (double quote) character. If this character appears any- where in the entered formula, AnalytiCalc will take the formula as text, regardless of whatever else is in it. You can also combine comments and formulas by using a separate state- ment and the *C construct (e.g. SUM[A3:A12]\SQRT(%)\*C SQUARE ROOT OF THE LENGTHS) of Calc. Once a cell is set up as numeric or text, it remains as it was originally set until changed (using the DF command, described below). The ET and E" forms of the Enter command force the expres- sion to be entered as non computable so that it is displayed rather than computed. The EV form forces it to be computed, regardless of whether any special characters exist. Numbers MUST have decimals to compute properly however. Thus "EV 45" is INCORRECT, where "EV 45." is CORRECT (assuming you want the value 45. to be computed; the first will not). If the decimals are omitted, the expressions may be considered integer. These will be converted, but the conversion has some limitations and is advised against. Also, integer division truncates results to produce an integer result (so that 7/2 would evaluate to 3, while 7./2. would evaluate to 3.5, for example). Thus, to avoid surprises, use decimals. Commands will be translated to upper case unless a " character appears anywhere in the command. If a " character is found, only the first 2 characters are converted. Most keys map to indirect files to make customizing AnalytiCalc easier. These files must be in your current directory to be found AnalytiCalc Reference Manual Page 23 (or a path search utility must be used). The system help file is called PCCHELP.HLP and must also be in your current directory to en- able the HELP command to work. However, where filenames are prompted for, AnalytiCalc can accept full pathnames. If the program cannot open a file for reading which is requested, it will attempt to create an empty file (and warn you of this on the top line of the screen). Within command files, it is sensible to permit entry from the console of commands after prompts. Therefore the % command has been added to facilitate such entry. There is an alternative method of handling prompts (the "-" command) but this one is somewhat more flexible. You will find examples of this in some of the .CMD files supplied with AnalytiCalc, and may use it in your own if desired. The operation is as follows: a line beginning in % is assumed to have several fields: %FLD-1%FLD-2%FLD-3 in which FLD-1 if present is typed out to the screen first. Then FLD-2 is passed as the command. If FLD-3 exists and starts with a ? or \, then the terminal is read and the result is added onto the end of FLD-2 before the command is passed back to the spread sheet. If the command begins with a space or control character or a \, then it is commented out and the command file falls past the % line. If however FLD-3 started with ? and the initial read-in character is not \ or a control character, the command file is rewound. This feature is used in the enter command file (AKD.CMD) to implement an "enter mode" somewhat akin to that of Visicalc, Supercalc, or others of that ilk by automatically inserting the "E " of the AnalytiCalc Enter com- mand until a \ is seen. If FLD-3 should be present and start with a & character, the auxiliary file is closed there and console input done as in \ input. (Note that a control-Z may be used to terminate Enter mode also, and F9 is preset to terminate it.) This func- tionality is probably less convenient than just using the /; command to go into "enter-mostly" mode now, and should probably be ignored. It is handy in templates to be used by novices though, since it gives extremely easy console data entry in a controlled fashion. Examples The expressions that are entered can be numbers or formulas re- lating numbers and other cells. They may also consist of several statements separated by a \ character which will all be evaluated at that cell, up to 110 characters. Thus, valid commands might be E 55.234 Enters value 55.234 at current cell E B4+H2+D5 Cell will contain the sum of cells B4,H2, and D5. E Totals Cell contains literal text "Totals". FORMULAS AnalytiCalc Reference Manual Page 24 Formulas may be used too. These include 2 classes. The first are the multi-argument functions, which either appear at the start of their statements or substatements (alone), or inside expressions. The difference between the classes is that the multiargument func- tions must have cell NAMES or RANGES as their arguments, while the single argument functions can have arbitrary Expressions in their ar- guments. Otherwise they may mix freely. All functions listed below may appear in command files or cells, and arguments must be cells, not expressions or constants. The IF statement, while it is treated as a function syntactically, may NOT appear inside general expres- sions, but must begin a cell equation or sub-equation (i.e., it may begin after a \ in a cell containing several operations). It cannot be used inside command files driving cells, but its functionality is supplied there by the "*J label" function, so no real limitation ex- ists on capability. The available multiple argument functions are: SUM[variables] Sum of all arguments MAX[variables] Max of arguments MIN[variables] Min of arguments AVG[variables] Average of arguments AVE[variables] Average of arguments ignoring cells containing 0.0 (e.g., labels) STD[variables] Standard deviation squared IF [V1.rel.V2] statement | else-statement AND[variables] Boolean AND of all variables in list IOR[variables] Boolean inclusive OR of variables NOT[variable] Boolean complement of variable XOR[v1,v2] Boolean exclusive OR of v1,v2 EQV[V1,V2] Boolean "equivalence" of V1,V2 (complement of exclusive OR, true if bits have the SAME value) CNT[variables] Number of nonzero variables in list MOD[V1,V2] Returns V1 modulo V2 (i.e., remainder of V1/V2 division.) SGN[v1] Returns 1.0 times sign of V1 LKP[var,variables] Lookup variable in "variables" range greater or equal to var, return its index (starting with 0) into variables range. LKN[var,variables] Lookup variable in "variables" range less than or equal to var, return its index (starting with 0) into variables range. LKE[var,variables] Lookup variable in "variables" range strictly equal to var, return its index (starting with 0) into range. Note all LKP, LKN, LKE return the last variable index if no satisfactory value found. NPV[disc,vars] Net Present Value of vars (equal time AnalytiCalc Reference Manual Page 25 interval numbers), at discount rate disc where disc is a fraction (e.g., .12 for 12%). Be sure the arguments are all cells, not literals, including the disc argument. Set a cell to the fraction desired for the disc argument and use its name in the arguments to NPV, rather than attempting to use a literal (e.g., 0.12) directly. IRR[PV,FV,returns] Internal Rate of Return. Will compute internal rate of return on up to 20 periods, returning rate per period. The returns are expected to be at equal time intervals. PV and FV are initial and final values of investment and the result is computed via Newton approximation. As with all functions here, be sure all arguments are cell names, not literal numbers. PMT[princ,inter,nper] Payment function. Generates payment given principal amount, interest per period (as a fraction), and number of periods. PVL[pmt,inter,nper] Present Value function. Generates present value of a loan given payment amount, interest rate, and number of periods. All financial functions assume that payments are for equal time intervals. RND[V1] Random number. Generates a random number distributed uniformly in the interval 0.0 to 1.0. An argument is required but never touched. CHS[Idx,Range] Choose cell from range based on value of cell Idx. If cell IDX contains 1, first element of range is chosen; if 2, second, etc. Zero is returned if out of range. Range can be any comma-delimited list of ranges also. ATM[A1,A2] Arc Tangent of A1/A2, but four quadrant version. Note these are single cells. In all cases except IF, arguments are variable names separated by commas, or variable ranges, or mixtures separated by columns. Note too that ALL THESE FUNCTIONS REQUIRE CELLS OR ACCUMULATORS AS ARGUMENTS; literals and single argument functions may NOT be used here. (That is why it is easy to place multiple commands on a line; the ACs or cells may be loaded in "separate" steps.) The IF [] state- ment is a partial exception since a range may be used to replace ei- ther variable inside the [] condition brackets. The sum of the ranges will be used in that case. A possible IF statement might look like IF [S2.LT.S5] R=2. | R=0. The Boolean variables are computed by taking the integer values of the variables and performing the appropriate boolean masking opera- tions on them. The values are returned as whatever type (floating or integer) the cell is. 32 bit integers are used. The CNT operation AnalytiCalc Reference Manual Page 26 will (generally) omit cells that have not been initialized, since these have a value of 0.0 and are not changed unless modified expli- citly. It may be used to compute a more accurate average than AVG where many uninitialized cells add zero values in. The quotient of the SUM divided by the CNT of a range will give an average of nonzero cells only. The LKP function quickly locates a cell in a list where the value is in a desired range; the *G function may be used to ex- tract other cells close to the addressed cell (e.g., beside it or below it). The LKN and LKE functions are exactly the same but search for lower or equal values instead of greater than values. This makes varied search strategies easy to do. These functions are most useful in user command files for selecting parts of the sheet according to criteria, as they allow very fast searches of large parts of the spreadsheet from inside command files. In this application the LKP class functions would be accessed via the TEst command. The NPV function is the standard financial Net Present Value function, assum- ing the discount rate is the fractional interest percent per period for the amounts in the range given, and that the range of numbers represents equal time periods. Remember to give a FRACTION rather than a Percent, so a 5% interest rate per period should be given as .05 for example. The IRR function will compute IRR given present value, future value, investments per period (for up to 20 periods), and assuming that the periods are equally spaced. The IRR value re- turned is per period and must be multiplied by periods per year to get a yearly rate. Note that other financial functions like PMT may be easily obtained by simple cell programs. The RND function is provided to give random numbers. This is useful in some types of Monte Carlo financial modelling. If distributions other than uniform ones of random numbers are desired, they may be obtained via standard mathematical transformations from this uniform distribution. The random number sequence is identical every time the spreadsheet is run. BE SURE IN USING THESE FUNCTIONS THAT ALL ARGUMENTS ARE IN SPREADSHEET CELLS OR ACCUMULATORS. LITERALS ARE NOT PERMITTED AS AR- GUMENTS. Thus, a command like "SUM[A1:A5,5.5]" is illegal. Instead, use a command like "A=5.5\SUM[A,A1:A5]" or "A=SUM[A1:A5]+5.5". The section on the interactive calculator (CALC) mode also men- tions the single argument functions available. These include normal trig and math functions like SQRT, SIN, COS, ATAN, AINT, ALOG, EXP, SINH, COSH, and some others. These single argument functions may be used in normal infix notation expressions, and do not have restric- tions on the types of arguments permitted them. See the section on Calc mode below for the full list. There are also a large number of functions that are syntactically commands, but which perform calcula- tions. These are also described in the Calc section and include date and matrix arithmetic and some addressing arithmetic functions.