AnalytiCalc The Analyst's Tool Reference Card and Summary There are 2 major command modes. The default is described here but the "/;" command enters the other and the "//" command goes back to the mode described in this reference. The second ("/;" mode) command format is more like other spreadsheets generally. Numbers and formulas are automatically ENTERed when typed at the start of a line. If you start a line with ", it is entered as text (minus the "). If you start a line with "/" (just the slash, not in quotes), the remainder of the line is taken as a command. Commands are NOT recognized unless preceded by / in this mode. Some of the auto keypad keys don't work well as defaulted in this mode. To facilitate writing usable command procedures, the /# command swaps the current mode with a saved mode. A command file can use this to save and restore the user's command mode and use a known mode internally. The sheet is displayed at the top part of the screen and a command cell in Row 23 is shown with the current position encoded. This starts off looking like A 1> ("Command-mostly" mode), or A 1: ("Enter-mostly" mode) When in "Enter-mostly" mode, begin all commands with / so that the exit command (X) is given as /X in that mode. It is a good idea to use AnalytiCalc in one mode or the other most of the time; switching modes can cause confusion. The commands available in AnalytiCalc are each described separately. The following is a brief listing alphabetically of their names. +J filename Start journaling to filename +N Close journal file < Rewind input file %prompt%cmd%key% Issue prompt and do cmd depending on key $ or } Perform operating system command > or >>pattern Search for formula containing or starting with pattern * Comment line -prompt Load arguments after prompt // Use "Command-Mostly" mode /; Use "Enter-Mostly" mode /# Swap current mode with save mode (cmd-mostly or ent-mostly) 1,2,3, or 4 Move cursor Up, Down, Left, or Right @file.typ Read file.typ as input instead of console AA nn {R/C} Add absolute nn rows or columns AR nn {R/C} Add relocating nn rows or columns (nn may be neg) CA in-range out-range Copy All (Absolute) CV in-range out-range Copy Value CF in-range out-range Copy Formula (and Format) CR in-range out-range Copy Relocating (all) DB ncol,nrow Display Bounds (no. cols,rows on screen) DF range [format] Display Format of range to format DL range {R/C}n:m Display Locate range as Row/Col to display col:row n:m DS{R/C}{A/D} n Display Sort row/col, Asc/Desc row/col n DT range {F/I} Display Type range as Float or Integer DW ncol,wid Display Width of col "ncol" to "wid" chars E expression Enter expression into cell E" expression Enter expression as text, no case translation ED 'oldstring'newstring' EDit cell ET expression Enter expression as UPPER CASE text EV expression Enter expression as computable formula F filename/nskip File read from filename onto display G Get saved sheet. Many variants. Hn HELP and show page n IR inrange outrange In Place Relocate inrange as if moved to outrange K Go into interactive calculator (need *V 3). *E goes back. L cell Go to cell M{0/1/2/3/4/5} Move - set move direction after enter MS or MH Macrocell Show or Hide - show or hide included files. OA cell Origin Absolute cell - Map Screen with cell in upper left OAD cell Origin Absolute Displace cell - Map Screen with cell in upper left leaving old windowing alone OR cell Origin Relative cell - map screen dwn/rght of cursor w/curs=cell ORD cell Origin Relative Displace cell - map screen down/right of cursor with cursor=cell leaving old windows OV + OVerride absolute refs, make relative OV - OVerride off - let abs refs be absolute P Put (save) spreadsheet. Many variations. R Recalculate sheet RB cell Set Relocate Boundary at cell RE Recalculate Entry - recalculate only cell entered. RF Recalculate, Force recalc of constants RI Recalculate Incremental - Recalculate only cell entered and displayed cells. RII Set Recalc Incremental flag but don't actually recalculate. RM Recalculate Manual - no recalc until R cmd S Setup - Global mapping/width/title setups TE expression TEst - Evaluate math expression in cmd mode - many variants. V View - Redraw screen VF View Formulas - Draw with Formulas instead of numbers VM View Manual - no screen repaint until a V cmd W Write display to file or printer. X eXit from AnalytiCalc. Asks for confirmation. ZE range ZEro range of cells ZA Zero All of sheet. Asks for confirmation. Also used for reinitializing. AnalytiCalc Function Summary 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 excluding zero args STD[variables] Standard deviation squared 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 interval numbers), at discount rate disc where disc is a fraction (e.g., .12 for 12%) 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. PMT[princ,inter,nper] Payment (mortgage payment per period) function. Will compute payment per period for principal amount "princ" with interest per period as "inter" and number of periods as "nper". All arguments must be cells. The formula is the standard ordinary annuity formula. Interest rate must be a fraction so that 14% would be 0.14, for example. PVL[payment,inter,nper] Present Value formula. Computes present value of an annuity given "payment", the payment per period, interest rate per period (as a fraction, so 12% is 0.12) in "inter", and number of periods as "nper". All arguments must be in cells. RND[DUM] Generates a random number between 0. and 1.0. An argument is needed but it is NOT touched by this function. CHS[V1,cells] Choose value from list of cells based on value of V1 cell. If V1 is 1, first cell in range is chosen. If V1=2, second cell is chosen and so on. If V1 is out of range zero is returned. Also sets ACs to have row, col of chosen cell like MIN, MAX, and LKx functions. IF [V1.rel.V2] statement | else-statement Compares two variables and executes either "statement" (if the relation is true) or "else-statement" (if the relation is false). Valid relations (in the place of the .rel. above) 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) The following single argument functions are available: FUNCT NAME ARG TYPE FUNCT VALUE DESCRIPTION ------------------------------------------------------- ABS REAL REAL absolute value DABS REAL REAL absolute value IABS INTEGER INTEGER absolute value IFIX REAL INTEGER REAL to INT conv. AINT REAL REAL REAL truncation INT REAL INTEGER REAL to INT conv. IDINT REAL INTEGER REAL to INT conv. EXP REAL REAL e**X DEXP REAL REAL e**X ALOG REAL REAL natural logarithm DLOG REAL REAL natural logarithm ALOG10 REAL REAL logarithm base 10 DLOG10 REAL REAL logarithm base 10 SQRT REAL REAL square root DSQRT REAL REAL square root SIN REAL REAL trigonometric sine DSIN REAL REAL trigonometric sine COS REAL REAL trig. cosine DCOS REAL REAL trig. cosine TANH REAL REAL hyperbolic tangent DTANH REAL REAL hyperbolic tangent ATAN REAL REAL arc tangent DATAN REAL REAL arc tangent The following special constructs in a formula act as functions: _@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 function to allow retrieval and edit of formulas. The *U XQTCM function permits use of the EDit command within a cell for string manipulation. The following "equation commands" also act as functions with the effects described: *@filename Where filename is the name of a file of CALC commands. CALC reads the file and executes the commands. *ASCII Declares a list of variables to be of type ASCII. *C COMMENT line. *N NOVIEW. *V VIEW. Controls printing options in K mode *R READ. Allows a single line to be read from the terminal. *REAL Declares specified variables to be REAL*8. *DECIMAL Declares specified variables to be REAL*8. *S STOP. Same as *E. Goes back to spreadsheet. *E EXIT. Gets out of K mode, back to spreadsheet mode. *Z ZERO. Zeroes all accumulators. *G *G V1,V2 (where V1 and V2 are cell or accumulator names) will evaluate V1 and V2 as the column and row numbers, on the physical sheet, of the desired cell. The addressed cell's value is retrieved and used as the resulting number. *W Takes the value at the current cell and writes it out to the formula as a numeric (float- ing) value. The *WF command stores the cell similarly, but uses the format of the current cell instead of the D32.25 format used for *W. *P The *P command resets the current cell coordinate from within a cell (until the next cell is evaluated only). It has several forms: *P - By itself, *P causes Calc to prompt for the new physical column and row number. *P V1 - This moves the current location to the named cell where V1 is the cell name (e.g. A5, H2) *P@ V1,V2 - This uses V1 and V2 (cell names) as column and row numbers and changes the current physical cell position to that defined by the contents of cells V1 and V2. This gives complete addressing of the sheet from within any cell. *F *F Label - If the value in % is positive and nonzero this command rewinds the input file for the AnalytiCalc @ command and seeks a line beginning with the characters *CLabel (where "label" is what you put after the *F command). *J *J Label - This command behaves as the *F command but operates on the file used by the Calc *@ command rather than the AnalytiCalc one. *QF *QW The *QF (Float) or *QW (write) commands are used to examine sequential files created outside AnalytiCalc and return values or formulas. Their syntax is *QF filename ?key1? ?key2? or *QW filename ?key1? ?key2? where ?key2? is optional l and m are delimiter characters for start and end of the parts of the selected records to extract (defaults to first part of the record) filename is just the file specifier in the host OS. The *QF command gets a part of a record containing the keys specified and turns it into a number ("floats" it). The *QW command Writes that part of the record to a formula. The special characters in the part of the com- mand are delimiters of the area to be picked out. Variant forms allow such choosing to be by column number instead of key character if needed. *U YRMOD VY,VM,VD returns the Julian date (in %) computed from the Year (in VY), the month (in VM) and the day (in VD), where VY, VM, and VD are sheet cells. These may be the result of date arithmetic. *U JDATE Var assumes the formula in cell Var (any spreadsheet cell name) contains a date string in the format YY/MM/DD. It reads this formula and converts the date to a Julian date, returning it in the % accumulator. *U JTOCH Jul,Var assumes that variable Jul (any spreadsheet cell) contains a Julian date and changes it into an ASCII string in the cell whose name is in the Var position here. *U DATE VY,VM,VD,Var uses VY, VM, and VD as year, month and day, and computes a Julian date from them. It then composes an ASCII string of form YY/MM/DD for that date and stores in the for- mula for Var. *U WKDYS D1,D2 computes the number of workdays between Julian dates D1 and D2 just as taking the difference of two Julian dates gives differences between calendar dates in days. *U WKDIN D1,N1 returns a Julian date that is N1 work days after the date in D1. *U IDATE() returns the current date as a Julian day. *U MTXEQ(AA:AA,XX:XX,BB:BB) solves equation AX=B where A, X, and B are matrices, and where the notation AA:AA means two cells at the upper left and lower right edges of matrix A (e.g. B2:C3 for the 2 X 2 matrix so defined), and the XX:XX and BB:BB notation means the same for the X and B matrices. *U MOVEV mtxa,mtxb moves values from mtxa to mtxb (useful prior to calling MTXEQ). *U MDET mtx This function computes and returns the determinant of matrix mtx. *U MPROD A,B,C This function will multiply matrix A by matrix B giving matrix C, provided that their dimensions are compatible. *U MADDV A,B,C This function adds matrix A to matrix B and stores in matrix C. All matrices must have the same dimensions. *U MSUBV A,B,C This function subtracts matrix B from matrix A leaving the result in matrix C. *U MMPYT A,B,C This function multiplies matrix A-transpose by matrix B and stores the result in matrix C; dimensions must be compatible. *U MMPYC A,B,K This function multiplies every element of matrix A by constant K. *U VARY X,A,W,I,P;Q;R;S;T;U;V;W (Equation Solving by Iterative Search) This function allows AnalytiCalc to automatically search for solutions to equations over up to 8 dimensions. The operation is that the accumulators named in the fields shown as P;Q;R;S;T;U;V;W (one to 8 may be specified, only one is required) are varied by a fraction W about their initial values (later scaled down by the gradient of the change in X) to attempt to get accumulator or cell X to equal accumulator or cell A. This is done for I iterations, where I is another accumulator. *U XQTCM command will execute the command (terminated by the end-of-line), with any command except X or K permitted, from inside a cell. This allows command files driven from cells to control moving data, etc. *U STRVL V1,start;len will return a value that is made from up to 8 characters in the FORMULA of cell V1 (where V1 is any cell name), starting at character "start" and for "len" characters. *U HERE will return the current location on the matrix and the current maximum row and column used. T, U get cell coords. *U FFTFW and *U FFTRV perform Fast Fourier Transforms in the forward and reverse (inverse) directions on the given range of data. *U LINEF Vy:Vy,Vx:Vx (with the Vx range optional) fits a line to the input range. y=Ux+T, err in %, corr. coeff. in W. *XV filename V1 *XF filename V1 The *X class commands are for sheet linkages. *XF loads a Formula from another saved spreadsheet, while *XV loads a Value. The following *U DBxxxxxx functions also exist as commands of form FILxxxxxx with the same results. *U DBOPINS range filename - Open input sequential on filename for cells in range. (Reads of input range formulas come from file when enabled.) *U DBOPINR range filename Open input random on filename for cells in range *U DBOPINU range filename Open range for update on filename for read and write as random access. *U DBCLSINP Close input *U DBCLSOUT Close output *U DBOPOUTS range filename Open output sequential from range cells to filename. (Writes of formulas in output range go to file.) *U DBOPOUTR range filename Open output random from range on filename *U DBENAINP Enable input file readin (initially disabled) *U DBENAOUT Enable output write (initially disabled) *U DBDISINP Disable input area readin *U DBDISOUT Disable output write *U DBEDTINP range Enables input and output and, for each cell in the given range, reads and writes the cell, allowing the file read/writes to take effect. Each cell is flagged as valid but of text type; the DF command must be used to reset any that should be treated as computable. Input and output are disabled on completion of the command. *U DBFMTOUT range *U DBVALOUT range Enables input and output and for each cell of the range takes the VALUE of the cell, reads the cell, sets it of text type, and writes the text equivalent of its value to the cell. *U DBCMPFRM V1:V2[,V3:V4] Compares two formulas. It returns, in the % accumulator, the index of the formula in cell V2 in the formula in cell V1. Lengths used are those of both formulas UNLESS the V3 and V4 cell arguments are seen. Then V3 and V4 are lengths of V1 and V2 strings used. Accum. W is set to comparison of V1 and V2 (-1/0/+1) *U DBLENFRM V1:V2 returns the length of the formula for cell V1 in the % accumulator and in cell V2 IF CELL V2 IS VALID. Otherwise the cell specified in V2 is ignored, though it must be present in the command or function. *U DBTRMFRM V1:V2,V3,V4 Reads the formula in V1 and uses V3 and V4 as start and end byte numbers within it. It returns to V2 the formula that is between the start and end bytes, trimming the V1 formula into V2 by chopping out the undesired parts.